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:
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 ;
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?
How to know/code for what happens if there is no match in accunit?
Would it be best to do a separate request against the accunit, once the accounts data was retrieved?
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.
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!
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
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?
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?
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.