How to code to retrieve data from two or more tables?

Hello all,

I need to obtain data from two or more tables. The data retrieved from the first table may be used to get data from the second and/or the third. Such as the following two simple, descrete commands.

SELECT lname, fname FROM accounts ;
SELECT unit FROM accunit WHERE accunit.fname = accounts.fname AND accunit.lname = accounts.lname:

My questions are:

  1. What is the correct way to do this in code. I have never coded for this kind of thing before, only simple SQL requests such as SELECT lname, fname FROM accounts ;
  2. How will the record set be organized? would it be all of the data from accounts, THEN the data from accunit? Or accounts / accunit then accounts / accunit and so on line by line back and forth?
  3. How to know/code for what happens if there is no match in accunit?
  4. Would it be best to do a separate request against the accunit, once the accounts data was retrieved?

Thanks all,
Tim

SELECT accounts.lname, accounts.fname, accunit.unit FROM accounts INNER JOIN accunit ON accnumit.fname = accounts.fname AND accunit.lname = accounts.lname;

This will give you a table of accounts where there is a matching accunit. You can add an ORDER BY clause at the end to sort the data. If you change the INNER JOIN to a LEFT OUTER JOIN then all accounts will be retrieved, but unit will be nil where there is no matching accunit record.

HTH
Wayne

Thanks Wayne!
SQL… 'tis another world! …

Is there somewhere to read, in simple terms, the definitions and uses of the JOIN methods mentioned? A lot of text out there is unnecessarily complicated, IMO. I realize that as skills and needs grow and change, more complexity is good. But an easy starter primer would be really helpful!

Thanks again,
Tim

Sorry Tim - you are calling for it:
http://lmgtfy.com/?q=sql+for+dummies+inner+join

Oliver! That was COOL!

I’ve never seen that displayed that way…
I did call for it, and I am glad you shared it!

Thanks for the smile tonite!
Tim

Wayne,

can you help me with the following:
I have 3 tables, Org_tbl, Pers_tbl and Cars_tbl.

The Org_tbl has 2 foreign keys P_ID (Pers_tbl) and C_ID (Cars_tbl) that point to the two other tables.
I want to see which Person has what Cars, but mind it is stored in Org_tbl, not in Pers_tbl.

so I tried

select * from Org_tbl, Pers_tbl, Cars_tbl where Pers_tbl.P_ID = Org_tbl.P_ID and Cars_tbl.C_ID = Org_tbl.C_ID

but I get no records…

select *
FROM org_tbl o,
pers_tbl p,
cars_tbl c,
where p.p_id=o.p_id
and c.c_id =o.c_id

would return any record in ORG that had a corresponding record in PERS AND CARS
Note in a multi table join you should not use “*” as you will have ambigious columns ( everyone has P_ID or C_ID)

So it may not be you are getting no records… it might be that you are getting an error that is not being handled properly

To follow up, is the JOIN used to determine which records are retrieved or which columns from selected records are retrieved? I ask since I need more columns from both tables. If I simply state additional column.field combinations as necessary?

Thanks,
Tim

The join determines how the tables are connected. Just add more fields before the FROM keyword.

Thank you!
Tim

What about adding a WHERE clause? I ran this SQL command and no results were returned. I expected nothing from units_accounts, but 20 records from units.

SELECT units.unit_id, units_accounts.fname, units_accounts.lname, units_accounts.access_code, units.last_access_activity, units.access_count FROM units INNER JOIN units_accounts ON units_accounts.unit_id =units.unit_id WHERE units.nrc = 'Y' OR units.bldg_no = 'NRC'  ;

Obviously there is something wrong with the SQL code. Can anyone point me to where it fell apart?

Thanks all!
TIm

INNER JOIN returns rows where there is data in both. You want an OUTER JOIN instead. In this case a LEFT JOIN.

select … from table1 … join table2 on …

LEFT JOIN returns all the matching rows in table1 along with any matching rows in table2.
RIGHT JOIN returns all the matching rows in table2 along with any matching rows in table1.
INNER JOIN returns only rows where there is a match in both table1 and table2.

Hi Tim, and thanks for your feedback! If the syntax was changed to LEFT JOIN, would the rest of the syntax be correct?

Thank you again!
Tim

Yes, it looks correct.

Thanks Tim.
I tried to edit the post, as I clicked inadvertantly! But the forum did not save my edit… oh well…
Thanks again for your feedback!
Tim

Works perfectly Tim,
Thanks again!
Tim