SQL Join question

Alejandrina

Hi all,

I wonder if anyone can help with this sql:

Table "Property_Name" catalogs properties: (property_id,
property_name)
Table "File_Property" catalogs files and their properties: (file_id,
property_id, property_value)

Data example:

Property_Name contains:

Property_ID Property_Name
---------------------------
1 Property_1
2 Property_2
3 Property_3

File_Property contains:

File_ID Property_ID Property_Value
----------------------------------------
111 1 AAA
111 2 BBB
111 3 CCC
222 1 XXX
222 3 ZZZ
333 2 GGG

I want get a list of ALL THE POSSIBLE PROPERTIES and show the values
where there are any:

111 1 Property_1 AAA
111 2 Property_2 BBB
111 3 Property_3 CCC
222 1 Property_1 XXX
222 2 Property_2 <null>
222 3 Property_3 ZZZ
333 1 Property_1 <NULL>
333 2 Property_2 GGG
333 3 Property_3 <null>

I tried all flavors of JOIN but none lists all the possible
properties. Can you help?

Thanks in advance,

Nov 12 '05 #1

Tokunaga T.

How about this?
(There may be better way.)
------------------------------ Commands Entered ------------------------------
SELECT fi.file_id
, pn.property_id
, pn.property_name
, fp.property_value
FROM (SELECT DISTINCT file_id
FROM File_Property
) AS fi
FULL OUTER JOIN
Property_Name pn
ON 0=0
LEFT OUTER JOIN
File_Property fp
ON fp.file_id = fi.file_id
AND fp.property_id = pn.property_id
ORDER BY
fi.file_id, pn.property_id
;
------------------------------------------------------------------------------

FILE_ID PROPERTY_ID PROPERTY_NAME PROPERTY_VALUE
----------- ----------- --------------- --------------
111 1 Property_1 AAA
111 2 Property_2 BBB
111 3 Property_3 CCC
222 1 Property_1 XXX
222 2 Property_2 -
222 3 Property_3 ZZZ
333 1 Property_1 -
333 2 Property_2 GGG
333 3 Property_3 -

9 record(s) selected.

Nov 12 '05 #2

Tokunaga T.

Some other variations.
(Not fully tested. But, these worked well on DB2 UDB for Windows and with your data)
1.
(
SELECT fp.file_id
, pn.property_id
, pn.property_name
, fp.property_value
FROM
Property_Name pn
LEFT OUTER JOIN
File_Property fp
ON fp.property_id = pn.property_id
UNION ALL
SELECT fp.file_id
, pn.property_id
, pn.property_name
, CAST(NULL AS CHAR(10)) AS property_value
FROM
Property_Name pn
INNER JOIN
File_Property fp
ON fp.property_id <> pn.property_id
WHERE NOT EXISTS
(SELECT *
FROM File_Property nx
WHERE nx.file_id = fp.file_id
AND nx.property_id = pn.property_id
)
AND fp.property_id
= (SELECT MIN(property_id)
FROM File_Property mn
WHERE mn.file_id = fp.file_id
AND mn.property_id <>pn.property_id
)
)
ORDER BY
file_id, property_id
;

2.
(
SELECT fp.file_id
, pn.property_id
, pn.property_name
, fp.property_value
FROM
Property_Name pn
LEFT OUTER JOIN
File_Property fp
ON fp.property_id = pn.property_id
UNION ALL
SELECT fp.file_id
, pn.property_id
, pn.property_name
, CAST(NULL AS CHAR(10)) AS property_value
FROM Property_Name pn
INNER JOIN
TABLE
(SELECT file_id
FROM TABLE
(SELECT file_id
, ROWNUMBER() OVER(PARTITION BY file_id) rn
FROM File_Property fp
WHERE NOT EXISTS
(SELECT *
FROM File_Property nx
WHERE nx.file_id = fp.file_id
AND nx.property_id = pn.property_id
)
) AS q
WHERE rn = 1
) AS fp
ON 0=0
)
ORDER BY
file_id, property_id
;

3.
SELECT fp.file_id
, pn.property_id
, pn.property_name
, CASE
WHEN fp.property_id = pn.property_id THEN
fp.property_value
END AS property_value
FROM
Property_Name pn
LEFT OUTER JOIN
File_Property fp
ON 0=0
WHERE fp.property_id = pn.property_id
OR
NOT EXISTS
(SELECT *
FROM File_Property nx
WHERE nx.file_id = fp.file_id
AND nx.property_id = pn.property_id
)
AND fp.property_id
= (SELECT MIN(property_id)
FROM File_Property mn
WHERE mn.file_id = fp.file_id
AND mn.property_id <>pn.property_id
)
ORDER BY
file_id, property_id
;

4.
SELECT fp.file_id
, pn.property_id
, pn.property_name
, CASE
WHEN fp.property_id = pn.property_id THEN
fp.property_value
END AS property_value
FROM
Property_Name pn
LEFT OUTER JOIN
TABLE
(SELECT *
FROM TABLE
(SELECT fp.*
, ROWNUMBER() OVER(PARTITION BY file_id) rn
FROM File_Property fp
WHERE fp.property_id = pn.property_id
OR
NOT EXISTS
(SELECT *
FROM File_Property nx
WHERE nx.file_id = fp.file_id
AND nx.property_id = pn.property_id
)
) AS q
WHERE rn = 1
) AS fp
ON 0=0
ORDER BY
file_id, property_id
;

Nov 12 '05 #3

Tokunaga T.

All JOINs in examples 1. to 4. can be INNER JOIN.
For example 4. can be written as followings.
4.
SELECT fp.file_id
, pn.property_id
, pn.property_name
, CASE
WHEN fp.property_id = pn.property_id THEN
fp.property_value
END AS property_value
FROM
Property_Name pn
, TABLE
(SELECT *
FROM TABLE
(SELECT fp.*
, ROWNUMBER() OVER(PARTITION BY file_id) rn
FROM File_Property fp
WHERE fp.property_id = pn.property_id
OR
NOT EXISTS
(SELECT *
FROM File_Property nx
WHERE nx.file_id = fp.file_id
AND nx.property_id = pn.property_id
)
) AS q
WHERE rn = 1
) AS fp
ORDER BY
file_id, property_id
;

Nov 12 '05 #4

Tokunaga T.

No need to use OUTER JOIN for 1st join.
Revised statement is as following.
SELECT fi.file_id
, pn.property_id
, pn.property_name
, fp.property_value
FROM (SELECT DISTINCT file_id
FROM File_Property
) AS fi
INNER JOIN
Property_Name pn
ON 0=0
LEFT OUTER JOIN
File_Property fp
ON fp.file_id = fi.file_id
AND fp.property_id = pn.property_id
ORDER BY
fi.file_id, pn.property_id
;

Nov 12 '05 #5

Alejandrina

to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81**************************@posting.google. com>...

No need to use OUTER JOIN for 1st join.
Revised statement is as following.
SELECT fi.file_id
, pn.property_id
, pn.property_name
, fp.property_value
FROM (SELECT DISTINCT file_id
FROM File_Property
) AS fi
INNER JOIN
Property_Name pn
ON 0=0
LEFT OUTER JOIN
File_Property fp
ON fp.file_id = fi.file_id
AND fp.property_id = pn.property_id
ORDER BY
fi.file_id, pn.property_id
;

Thanks so much! It works like a charm! Now, of course, If have to
study it so I can apply it elsewhere.

Alejandrina

Nov 12 '05 #6