Re: Joining a number of tables in a MySQL query

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 - chris@whitecrown.net WhiteCrown Networks - More Than White Hats Web Application Security - www.whitecrown.net */ ----- Original Message ----- From: Mike Gifford <mike@openconcept.ca> To: <php-general@lists.php.net> 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: php-general-unsubscribe@lists.php.net | For additional commands, e-mail: php-general-help@lists.php.net | To contact the list administrators, e-mail: php-list-admin@lists.php.net | | |