How to join records from multiple object tables to a master table with a single query?

Question

So I have a data model which is set up with a table that contains NAME, ID, and CONDITION columns for a series of objects (each object has a unique id number). The rest of the attributes for these objects are contained in columns of several respective tables based on the object type (there are some different attributes associated with each type). All the type-specific tables have an ID column so that the objects can be matched to the master list.

I want to write an sql query that will return information about objects of several different types based on the CONDITION tied to their unique ID.

Here is a simplified example of what I am working with:

object_master_list
| ID | NAME | CONDITION |
-------------------------
|1234|  obj1|       true|
|0000|  obj2|      false|
|1236|  obj3|       true|
|0001|  obj4|      false|
|5832|  obj5|       true|
|6698|  obj6|      false|
|6699|  obj7|      false|


obj_type_one
| ID | NAME |   HEIGHT   |
-------------------------
|1234|  obj1|    o1height|
|0000|  obj2|    o2height|
|5832|  obj5|    o5height|
|6699|  obj7|    o7height|


obj_type_two
| ID | NAME |   WEIGHT   |
-------------------------
|1236|  obj3|    o3height|
|0001|  obj4|    o4height|
|6698|  obj6|    o6height|

As you can see, there is no correlation between NAME and type or ID and type. I am currently working in iReport, and I have been using the query designer and editing it manually as necessary.

Right now an example query would look like:

SELECT 
    object_master_list."NAME" AS NAME,
    obj_type_one."HEIGHT" AS HEIGHT,
    obj_type_two."WEIGHT" AS WEIGHT
FROM
    object_master_list INNER JOIN obj_type_one ON object_master_list."ID" =    
obj_type_one."ID"
    INNER JOIN obj_type_two ON obj_type_two."ID" = object_master_list."ID"
WHERE
    object_master_list."CONDITION" = 'true'

My data is returning no results. From the research I have done on sql joins, I believe this is happening:

Where circle "A" represents my master list.

iReport stores and utilizes the values returned from a query row by row, with a field for each column. So ideally I should end up with this:

$F{NAME} which will receive the following values in succession ("obj1", "obj3", "obj5") 
$F{HEIGHT}  with value series (o1hieght, null, o5height)
$F{HEIGHT} with value series (null, o3weight, null)

The table representation I suppose would look like this:

| NAME |  HEIGHT  |  WEIGHT  |
------------------------------
|  obj1|  o1height|      null|
|  obj3|      null|  o3weight|
|  obj5|  o5height|      null|

My question is how do I accomplish this?

I ran in to this on a smaller scale before, so I am aware that I could use subreports or create multiple data sets, but frankly I have a lot of object types and I would rather not if I could help it. I am also not allowed to add a TYPE column to the master list.

Thanks in advance for any replies.

Answer 1

You can use left join in the following way :

select o1.name, o2.height, o3.weight
from object_master_list o1 left join obj_type_one o2 on o1.id = o2.id 
                           left join obj_type_two o3 on o1.id = o3.id
where o1.condition = 'true'

SQL Fiddle