SQL query with union and/or outer join needed

Question

i could use some help. This is a real-life problem, not homework. I tried all kinds of outer join and union statements, but I am just making a mess.

I have 3 tables:

Table "item":
id | name
---------
1  | 'Item 1'
2  | 'Item 2'

Table "property_type":
id | name
------------------
105 | 'Property A'
106 | 'Property B'
107 | 'Property C'

Table "property" (fk stands for foreign key):
id   | fk_item | fk_property_type | value
---------------------------------------------------------------------
1044 | 1       | 106              | 'some value for prop B of item 1'
1045 | 2       | 107              | 'some value for prop C of item 2'

Now I need a statement that produces a property table for a given item id, showing a line for every property type, even if not all properties are set for that item, e.g. if I am interested in item 1, the output should be:

item_name | property_type_name | property_value
------------------------------------------------------------------
'Item 1'  | 'Property A'       | NULL
'Item 1'  | 'Property B'       | 'some value for prop B of item 1'
'Item 1'  | 'Property C'       | NULL

And feel free to suggest a better question title. If I knew how to title this question better I would probably have found the answer by searching myself.

That's always going to be a mess. If you insiste on going down the EAV route, what you need is an other table DefaultProperties which would link Item to PropertyType. At the moment all you could get is a cartesian product which would only be acceptable if all items have all properties, for ever. — Tony Hopkinson, Oct 05 '13 at 17:58
PS you call also have a default_value for pre-populating if that might come in handy. — Tony Hopkinson, Oct 05 '13 at 17:59
What is EAV? The default values are simply NULL, isn't there a way to build this default value table as temporary table on the fly inside the statement? — Reto Höhener, Oct 05 '13 at 18:02
Entity Attribute Value. Google it, most articles on it will start. Don't do this. Yes of course you can build a temp table, doesn't mean you should though... — Tony Hopkinson, Oct 05 '13 at 18:30

score 2 · Accepted Answer · answered Oct 05 '13 at 17:59

2

SELECT 
    i  . name   AS  item_name,
    pt . name   AS  property_type_name,
    p  . value  AS  property_value
FROM 
    item AS i
  CROSS JOIN
    property_type AS pt
  LEFT JOIN 
    property AS p
      ON  p.fk_item = i.id
      AND p.fk_property_type = pt.id
WHERE
    i.id = 1 ;                      -- given item ID

answered Oct 05 '13 at 17:59

ypercubeᵀᴹ

113,259
19
174
235

Great, that turned out simpler than I had feared it would! I had always tried to select initially from the 'property_type' table. Thank you very much! – Reto Höhener Oct 05 '13 at 18:18

SQL query with union and/or outer join needed

1 Answers1