5

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: sql join isue

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.

Alex K
  • 22,315
  • 19
  • 108
  • 236
Sam Hazleton
  • 470
  • 1
  • 5
  • 21

1 Answers1

2

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

Grisha Weintraub
  • 7,803
  • 1
  • 25
  • 45
  • will this not result in duplicate entries from the master list? – Sam Hazleton Jul 24 '13 at 17:48
  • 2
    @GrishaWeintraub Thanks for the link. I also tried something with your link and I think double left join might not correct for what San wanted? For example, if the master table has iDs from 1 to 10, table one has 1-4 and table two has 6-9, then the result of double left join will include from 1-10, which I think is different from what Sam wanted which is 1-4 and 6-9 (no 5 and 10)? – J.DoG Sep 14 '17 at 08:23
  • @J.DoG In this case, both height and weight will be nulls, and if you don't want them in the result you can easily filter them out by adding the following to the where clause: " and not (height is null and weight is null)". – Grisha Weintraub Sep 14 '17 at 15:30