CODEWITHSUNDEEP
sqloracle-database
user10086705
user10086705

Reputation:

Select all where one value (with a criteria) is higher then a value in a other table (with different criteria)

What i want is the ID number of either Table 1 or 3 Where the value of a specific color is higher then that of a specific color in the other table.

Like: Select the ID's from Table 1 where the value of the Color 'Blue' in table1 is higher then the value of the color 'Purple'in table3 where the ID's match.

Table1 

ID  |   Color   |   KeyA    |   KeyB
1   |   Blue    |   AB      |   12
2   |   Red     |   CD      |   34
3   |   Green   |   EF      |   56
1   |   Pink    |   EF      |   7

Table2 

KeyA    |   KeyB    |   Value       
AB      |   12      |   10      
AB      |   34      |   20      
EF      |   56      |   40

Table3 

ID  |   Color   |   KeyC    |   KeyD
1   |   Purple  |   QW      |   15
2   |   Yellow  |   ER      |   45
3   |   Orange  |   TY      |   78

Table4 

KeyC    |   KeyD    |   Value       
QW      |   15      |   8       
TY      |   45      |   26      
TY      |   78      |   67

Table 1 and 3 are connected through the ID (NOT unique) Table 1 and 2 are connected on their KeyA and KeyB Table 3 and 4 are connected on their KeyC and KeyD

It's probably something simple but I've been staring at this for to long to see it.

Eddit: The original Question has been answered by scaisEdge, but i have a follow up question.

Is it also possible to Select the ID from Table1, where the corresponding Value of the row where the Color is 'Blue' is higher then the corresponding Value of the row where the Color is 'Pink'?

Upvotes: 1

Views: 56

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You should use INNER JOIN for all the table eg: 

select t1.ID 
from table1 t1 
inner join  Table2 t2 ON t1.KeyA = t2.KeyA AND t1.KeyB = t2.KeyB 
inner join Table3 t3 ON t1.ID = t3.ID 
inner join Table2 t4 ON t3.KeyC = t4.KeyC AND t3.KeyD = t4.KeyD 
WHERE  t2.value > t4.value

and id for blu and pink 

select t1.ID 
from table1 t1 
inner join  Table2 t2 ON t1.KeyA = t2.KeyA AND t1.KeyB = t2.KeyB 
inner join Table3 t3 ON t1.ID = t3.ID 
inner join Table2 t4 ON t3.KeyC = t4.KeyC AND t3.KeyD = t4.KeyD 
INNER JOIN table1 t5 on t1.id = t5.id and t1.color='Blue' and t5.color='Pink'
WHERE  t2.value > t4.value

Upvotes: 3

Related Questions