Re: selecting from multiple tables on one database
| From: | Phil Jackson | Date: | Sun, 22 Apr 2001 05:32:39 +0000 |
| Subject: | Re: selecting from multiple tables on one database | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-8780@lists.php.net to get a copy of this message | ||
Try using :
SELECT table1.UserName
, table1.City
, table1.State
, table2.Sex
, table2.Age
,table3.SpouseAge
FROM table1
, table2
, table3
WHERE table1.UserName = table2.UserName
AND table1.UserName = table3.UserName
You have to have a common field upon which to join the tables - I'm suprised you only
got 12 rows back - you haven't given any selection criteria for tables 2 and
3! Also, if you'll format your queries as above (with whatever line continuation
character PHP requires, if any...don't remember!) you will find them easier to read,
maintain, and also spot obvious errors.
Hope this helps,
Phil J.
Todd Pillars wrote:
> I have checked the mysql.com docs and phpbuilder.com and zend.com and I am
> more confused than ever on selecting a single common record from multiple
> tables. Here is what I have so far.
>
> MySQL 3.23.22
> PHP 4.0.3pl1
> RedHat 6.1
>
> members
>
> table1
> ID # auto_increment
> UserName # unique
> Name
> Address
> City
> State
> ZIP
>
> table2
> ID # auto_increment independent of table1
> UserName # unique
> Birthday
> Sex
> Age
>
> table3
> ID # auto_increment independent of table1 and table2
> UserName
> SpouseName
> SpouseBirthay
> SpouseAge
> DateTime # last update
>
> I am trying to pull out UserName, City, State from table1 - Sex, Age from
> table2 - and Age from table3 where the ID number of table1 is '1'; Meaning I
> only want one row containing the common information.
>
> If I use the following select statement I get 12 rows of data that do not
> contain what I am looking for!
>
> SELECT table1.UserName, table1.City, table1.State, table2.Sex, table2.Age,
> table3.SpouseAge FROM table1, table2, table3 WHERE table1.ID = '1'
>
> Can someone help please, or at least send me in the right direction?
>
> t.
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net
> For additional commands, e-mail: php-db-help@lists.php.net
> To contact the list administrators, e-mail: php-list-admin@lists.php.net