single select query spanning multiple tables

here are my tables and fields

[gen_objects] -> object_id, object_title_eng, ...

[subsections] -> subsection_id, subsection_title_eng, ...

[assocs] -> subsection_id, gen_object_id, ... (+ other table ids)

the objects and subsections are associated via the assocs table. what i need is a single sql statement that return a recordset of all objects (ids and titles) along with their associated subsections (ids and title). Note that not all objects are associated to subsections which is my problem at the moment. I know this is probably easy to do but it is giving me a headache now!

Thanks in advance for your help...
lildrcAsked:
Who is Participating?
 
adatheladCommented:
Ah ok, give this a try:

SELECT o.gen_object_id, o.object_title_eng, s.subsection_id, s.subsection_title_eng
FROM gen_objects o
    LEFT JOIN assocs a ON o.object_id = a.gen_object_id AND a.subsection_id IS NOT NULL
    LEFT JOIN subsections s ON a.subsection_id = s.subsection_id
ORDER BY o.object_id
0
 
adatheladCommented:
SELECT o.object_id, o.object_title_eng, s.subsection_id, s.subsection_title_eng
FROM gen_objects o
    LEFT JOIN assocs a ON o.object_id = a.gen_object_id
    LEFT JOIN subsections s ON a.subsection_id = s.subsection_id
ORDER BY o.object_id
0
 
adatheladCommented:
That will return all gen objects regardless of whether or not they have any subsections - it sounded like this is what you want? If not, you can just change the "LEFT JOIN" statements with "INNER JOIN" which will only return gen objects that DO have subsections
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
lildrcAuthor Commented:
sorry, first line should be:

[gen_objects] -> gen_object_id, gen_object_title_eng, ...

but i've changed that in your query and i get duplicate objects.
0
 
adatheladCommented:
If a gen_object has more than one subsection, then you will get multiple records for the same object but each with different subsection details.

So, how do you want the data displayed? Perhaps you can post up a sample output
0
 
lildrcAuthor Commented:
a gen_object can only belong to one subsection. at the moment i have 61 records in my gen_objects table and your query returns 73 records.

i think this might be due to the fact that some gen_objects are associated to other tables in the assoc table leaving null values in the subsection_id column...
0
 
lildrcAuthor Commented:
i tried that as well but it still returns 72 records :(

putting != null returns 61 but has null values for the subsection fields!
0
 
adatheladCommented:
SELECT o.gen_object_id, o.object_title_eng, s.subsection_id, s.subsection_title_eng
FROM gen_objects o
    INNER JOIN assocs a ON o.object_id = a.gen_object_id
    INNER JOIN subsections s ON a.subsection_id = s.subsection_id
ORDER BY o.object_id

This will *only* return objects that do have a subsection - give this a try and see if this is what you are after
0
 
lildrcAuthor Commented:
i need the unassociated ones as well. whats wierd is that i tried the inner join query, then changed the inner to left and added the not null line and it now works!

SELECT o.gen_object_id, o.gen_object_title_eng, s.subsection_id, s.subsection_title_eng
FROM gen_objects o
    LEFT JOIN assocs a ON o.gen_object_id = a.gen_object_id AND a.subsection_id IS NOT NULL
    LEFT JOIN subsections s ON a.subsection_id = s.subsection_id
ORDER BY o.gen_object_id

i've never used left join before - i'll have to read up on it - thanks a lot for your help!
0
 
adatheladCommented:
No problem.

A LEFT JOIN means that all records will be returned from the left hand table regardless of whether there are any matching records in the table on the right hand side of the join.
So in this case, all records from gen_objects, regardless of whether there is a subsection association linked to it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.