Universal SQL query for different input elements

Question

I have a SQL query problem with the following abstract sample context: There are 2 different input data for my sql query defined as ''MainElement'' with key 123 for the one and 789 for the other main element.

Further I have a table called Relation with columns pk, FirstElement, SecondElement and ThirdElement.

Furthermore there is a table called Props with the columns pk, name and valueString. The special feature about this context is that column name in Props defines 2 further columns called 4thElement and 5thElementof table Relation as a row with its values in column valueString .

|pk | 1stElement | 2ndElement | 3rdElement |
|abc|-----123----|-----456----|-----null---|
|def|-----789----|-----101112-|---131415---|

|Pk | Name | ValueString |
|def|4thElement|161718---|
|def|5thElement|ghi------|

As you can see the MainElement 789 has a value for 4thElement and 5thElement in Props, but MainElement 123 hasn't any value in Props.

What I need is an universal SQL query with input value 1stElement e.g., 123 or 789 that returns me a result for both main elements independent of the fact that MainElement 123 hasn't any value in Props.

Sample result:

|1stElement | 2ndElement | 3rdElement | 4thElement | 5thElement |
|123--------|------456---|-----null---|---null-----|----null----|
|789--------|----101112--|---131415---|---161718---|----ghi-----|

I am using Oracle SQL Developer.

Select 
rel.1stElement,
....
From 
Relation rel,
Props pro,
Where 
?

Thanks in advance.

You say that input value will be "123 or 789", but then want to have "result for both main elements". Isn't it a self-contradictory? Please clarify. — PM 77-1
– PM 77-1, Commented Apr 17, 2013 at 14:27
I don't understand why this should be self-contradictory? I want to use this sql query in general for both inputs. First sql query with input 123 and secondly with 789 or vice versa. Not both simultaneously for an execution. The problem is that I get an empty result when I have input 123 because in table Props I haven't any entry with pk abc ;-))) — ugurkocak1980
– ugurkocak1980, Commented Apr 18, 2013 at 7:48
You query outline hints at INNER JOIN that eliminates any NULL values. OUTER JOIN should take care of it. It's not clear if you can have multiple values of 4thElement and 5thElement for the same pk. In either way, it seems that the already suggested answer would do the job. If it does not, you will need to clarify your requirements. — PM 77-1
– PM 77-1, Commented Apr 18, 2013 at 16:39

Cyril Gandon · Accepted Answer · 2013-04-17 14:25:47Z

1

This should do the work, this is typically a pivot query that you need:

SELECT rel.pk, rel.1stElement, rel.2ndElement, rel.3rdElement 
     , MAX(CASE WHEN pro.Name = '4thElement' 
           THEN pro.ValueString 
           ELSE NULL
           END) as 4thElement
     , MAX(CASE WHEN pro.Name = '5thElement' 
           THEN pro.ValueString
           ELSE NULL
           END) as 5thElement
FROM Relation rel 
    LEFT OUTER JOIN Props pro 
        ON rel.pk = pro.pk
GROUP BY rel.pk, rel.1stElement, rel.2ndElement, rel.3rdElement

answered Apr 17, 2013 at 14:25

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

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

Collectives™ on Stack Overflow

Universal SQL query for different input elements

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