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, 5 4918
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.
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
;
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
;
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
; 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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:
|
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:
|
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
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
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
|
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...
|
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...
|
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...
|
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,...
|
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,...
| |