• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

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...
0
lildrc
Asked:
lildrc
  • 6
  • 4
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now