Re: [PHP] Joining a number of tables in a MySQL query

[Chris Lambert - WhiteCrown Networks]

SELECT
 WLPaddress.city,
 WLPaddress.state,
 WLPbib.publisher,
 WLPbib.title,
 WLPbib.publicationDate,
 WLPprofile.firstName,
 WLPprofile.lastName,
 WLPprofile.organization,
 WLPcountry.languageName
FROM
 WLPbib
LEFT JOIN
 WLPprofile
  ON
 WLPprofile.profileID = WLPbib.profileID
LEFT JOIN
 WLPaddress
  ON
 WLPaddress.publisherID = WLPbib.publisherID
LEFT JOIN
 WLPcountry
  ON
 WLPcountry.countryID = WLPaddress.countryID

/* Chris Lambert, CTO - [EMAIL PROTECTED]
WhiteCrown Networks - More Than White Hats
Web Application Security - www.whitecrown.net
*/

----- Original Message -----
From: Mike Gifford <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, July 11, 2001 6:40 AM
Subject: [PHP] Joining a number of tables in a MySQL query


| Hello,
|
| I'm trying to relate four tables within a single query and am running into
| difficulties.  Heck, I don't even know if it is possible.  Most of the
| explainations I see are for two tables, not four...
|
| So I'm trying to do something like this:
|
| SELECT  address.city,
|        address.state,
|          bib.publisher,
|          bib.title,
|          bib.publicationDate,
|          profile.firstName,
|          profile.lastName,
|          profile.organization,
|          country.languageName
| FROM WLPbib bib
| JOIN ON  WLPprofile profile USING (profileID)
| JOIN ON WLPaddress address USING (publisherID)
| JOIN ON WLPcountry country USING (countryID)
|
| I don't know how to express the following relationship in the query above:
|    The tables WLPbib & WLPprofile share the value profileID
|    WLPbib and WLPaddress share the value publisherID
|    WLPaddress and WLPcountry share the value countryID
|
| I also used JOIN ON, but didn't know if any of these others would be more
| appropriate:
|
| Cross Join
| select c.name, o.cid from orders o, clients c where o.cid = "acm-042";
|
| equijoin
| select p.os, c.name from orders o, pcs p, clients c where p.pid=o.pid and
o.pid
| = 1 and o.cid=c.cid";
|
| non-equijoin
| SELECT p.os, o.pid from orders o, pcs p where o.pid > p.pid;
|
| Left Join
| select * from orders left join pcs on orders.pid = pcs.pid";
| select * from orders left join pcs on pcs.pid = 3 and orders.pid =
pcs.pid;
|
| Using Option
| SELECT * from clients join on orders where clients.cid = orders.cid;
| SELECT * from clients join on orders using (cid);
| --
| Mike Gifford, OpenConcept Consulting, http://openconcept.ca
| Offering everything your organization needs for an effective web site.
| Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
| It is a miracle that curiosity survives formal education. - A Einstein
|
|
| --
| PHP General Mailing List (http://www.php.net/)
| To unsubscribe, e-mail: [EMAIL PROTECTED]
| For additional commands, e-mail: [EMAIL PROTECTED]
| To contact the list administrators, e-mail: [EMAIL PROTECTED]
|
|
|


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]