How do I join 3 or more tables?

[Cep]

Hello!

I have just figured out how to join 2 tables together to get information out of a single select query but now I want to retrieve information from possible 3 or more tables. I am using Access as my database but its mainly how do I construct the SQL string?

Here is my original,

[code=php:0]
SELECT BsysBudgetBooklet.*, BsysOrders.* FROM BsysOrders INNER JOIN BsysBudgetBooklet ON BsysBudgetBooklet.bborderID = BsysOrders.bborderID WHERE BsysBudgetBooklet.byearID = {$byear}{$bbsqlend}";
[/code]

And here is my "not working" second version to include a new table DEClient
[code=php:0]
$bbsql = "SELECT BsysBudgetBooklet.*, BsysOrders.*, DEClient.Audit FROM BsysOrders INNER JOIN BsysBudgetBooklet ON BsysBudgetBooklet.bborderID = BsysOrders.bborderID AND INNER JOIN DEClient ON DEClient.ID = BsysOrders.clientID WHERE BsysBudgetBooklet.byearID = {$byear}{$bbsqlend}";
[/code]

Please dont worry about the variables at the end they are not important.

[realjumper]

Can I *bump* this question up, because I would like to know this as well. Thanks

[craygo]

There are a couple of ways to join tables. Thru the where clause or with the join clause. Up to you. here is the fix for yours

[code]<?php
$bbsql = "SELECT BsysBudgetBooklet.*, BsysOrders.*, DEClient.Audit FROM BsysOrders
INNER JOIN BsysBudgetBooklet ON BsysOrders.bborderID = BsysBudgetBooklet.bborderID
INNER JOIN DEClient ON BsysOrders.clientID = DEClient.ID WHERE BsysBudgetBooklet.byearID = {$byear}{$bbsqlend}"
?>[/code]

So what you are doing is selecting BsysOrders and joining it to 2 other tables. If you look at other FROM clauses they are seperated by comma's not "AND" so you shouldn't use AND in your FROM clause

Ray

[realjumper]

Okay....here is one of my quries that works fine.....'sid' is the common (unique) field

[code]
SELECT n.sid, n.first_name, n.last_name
FROM true_name n
INNER JOIN service_permissions s ON n.sid = s.sid
WHERE pr_uploads = 'Yes';
[/code]

If I have a third table, which also has 'sid' as the common (unique) field, I could INNER JOIN all 3 tables on 'sid'. Correct?

Thanks :-)

[craygo]

yes you should.

[code]SELECT n.sid, n.first_name, n.last_name
FROM true_name n
INNER JOIN service_permissions s ON n.sid = s.sid
INNER JOIN new_table m ON n.sid = m.sid
WHERE pr_uploads = 'Yes';[/code]

What you have to remember is that the table true_name is the table that is common to both tables. If you have a table that joins one table then joins the other, then you would use the middle table. 

Example   
customer.customerID = order.customerID
order.detailID = details.detailID

In this case you would not use the first table, customers, because it is not common to details. But order is, so your query would be
[code]SELECT *
FROM order o
INNER JOIN customer c ON o.customerID = c.customerID
INNER JOIN details d ON o.detailID = d.detailID
WHERE c.customerID = '1';[/code]

Ray

[realjumper]

Ahhhh.....I see!!!

Many thanks Ray :)

[craygo]

NP  ;D

[Cep]

Thanks for your assistance Ray, your version works perfectly for MySQL but as I mentioned this is for Access ;) You may not be aware that it is very pesky about its syntax to the point of being ridiculous but what else can we expect from Microsoft.

So after much hair pulling and swearing I present to everyone the Access version of joining more than 1 table together,
[code=php:0]
$bbsql = "SELECT BsysBudgetBooklet.*, BsysOrders.*, DEClient.* FROM DEClient INNER JOIN (BsysBudgetBooklet INNER JOIN BsysOrders ON BsysBudgetBooklet.bborderID = BsysOrders.bborderID) ON DEClient.ID = BsysOrders.clientID WHERE BsysBudgetBooklet.byearID = {$byear}{$bbsqlend}";
[/code]