473,405 Members | 2,210 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

SQL Join question

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

46
by: Leo Breebaart | last post by:
I've tried Googling for this, but practically all discussions on str.join() focus on the yuck-ugly-shouldn't-it-be-a-list-method? issue, which is not my problem/question at all. What I can't...
0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
1
by: Paul Bramscher | last post by:
Here's one for pathological SQL programmers. I've got a table of things called elements. They're components, sort of like amino acids, which come together to form complex web pages -- as nodes...
4
by: eXavier | last post by:
Hello, I have query joining several tables, the last table is joined with LEFT JOIN. The last table has more then million rows and execution plan shows table scan on it. I have indexed columns...
8
by: kieran | last post by:
Hi, I have the following sql statement. I originally had the statement with two INNER JOINS but in some situations was getting an error so changed the last INNER JOIN to a LEFT OUTER JOIN (as...
3
by: Jack Smith | last post by:
Hello, I want to be able to view data from 3 tables using the JOIN statement, but I'm not sure of how to do it. I think i don't know the syntax of the joins.I imagine this is easy for the...
3
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
7
by: germanshorthairpointer | last post by:
Hello, I'm trying to do a join based on the following tables: Person(person_id,person_name) Grade(grade_id,grade_person_id,grade_score) The data looks like this: Person:
27
by: Paulo da Silva | last post by:
Hi! I was told in this NG that string is obsolet. I should use str methods. So, how do I join a list of strings delimited by a given char, let's say ','? Old way:
12
by: Chamnap | last post by:
Hello, everyone I have one question about the standard join and inner join, which one is faster and more reliable? Can you recommend me to use? Please, explain me... Thanks Chamnap
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.