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

[Mike Gifford]

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
FROMWLPbib 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]

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]