Getting columns of multiple SQL tables in a single query

Question

I want to get all the metadata values related to a document in a single query, be it with null in some columns.

Example:

DOCUMENT table     ATTRIBUTE_1 values    ATTRIBUTE_2 values

Id | Name          DocId | Value         DocId | Value
-----------        ---------------       ---------------
1  | Doc1          1     | Val1          1     | ValA
2  | Doc2          1     | Val2          1     | ValB
                                         1     | ValC

For Doc1, I want the query to return the values for each attribute ordered alphabetically:

Attr1     | Attr2
--------------------
Val1      | ValA
Val2      | ValB
NULL      | ValC

I tried a very naive query:

SELECT a1.Value, a2.Value FROM ATTRIBUTE_1 a1, ATTRIBUTE_2 a2, DOCUMENT d 
WHERE d.Id = a1.DocId AND d.Id = a2.DocId AND d.Id = 1

I tried doing inner joins, I tried googling but couldn't find terms which weren't about merging multiple columns in a single one.

The database used is Oracle.

How can I achieve this goal?

Thank you

How are the "attributes" ordered? In other words, why is the third row in you expected results NULL | ValC? — Colin 't Hart
– Colin 't Hart, Commented Oct 17, 2013 at 13:35
What connection is there between Val1 and Val2 so that they must appear side-by-side? (the same goes for all pairs in the result) — geomagas
– geomagas, Commented Oct 17, 2013 at 13:36
@Colin'tHart I added some details about what I tried. The sorting should be alphabetical then NULL. @geomagas Do you mean Val1 and ValA? If yes, the columns only need to be sorted alphabetically. — Valentin
– Valentin, Commented Oct 17, 2013 at 13:58
I hope Val1, Val2, ... are just examples, because Val10 will sort before Val9. — Colin 't Hart
– Colin 't Hart, Commented Oct 17, 2013 at 14:02
@Colin'tHart Yes these are just silly examples because I don't have the right to send the real values outside of my company. — Valentin
– Valentin, Commented Oct 17, 2013 at 14:05

Colin 't Hart · Accepted Answer · 2013-10-17 14:26:11Z

1

This works for me:

with a1 as (
   select
     row_number() over (partition by DocId order by Value) as r,
     DocId,
     value
   from
     ATTRIBUTE_1
), a2 as (
   select
     row_number() over (partition by DocId order by Value) as r,
     DocId,
     value
   from
     ATTRIBUTE_2
)
select a1.Value as Attr1, a2.Value as Attr2
from a1 full outer join a2 using (DocId, r) join DOCUMENT on (DocId = Id);

See this SQL Fiddle: http://sqlfiddle.com/#!4/a3526/3/0

edited Oct 17, 2013 at 14:26

answered Oct 17, 2013 at 14:17

Colin 't Hart

7,8084 gold badges34 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting columns of multiple SQL tables in a single query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related