I am a newbie to writing SQL queries, Can anyone please help me write SQL query for below conditions and joins?
I have 4 tables let's say T1
, T2
, T3
and T4
T1 have columns a1(PK), a2, a3, a4
T2 have columns b1(PK), a1(FK), b2
T3 have columns c1(PK), a1(FK)
T4 have columns d1(PK), c1(FK), d2, d3
Conditions :
I want to have all columns from T1
when a4 == "xx"
If T1.a1 == T2.a1
and T1.a2 == "x"
, then I want b2
to be included in the final result as new column.
If T3.c1 == T4.c1
and T4.d2 == "y"
, then take value from d3
which needs to be joined to step4
If T1.a1 == T3.a1
and T1.a2 == "z"
, then value from d3
(step 3) should be included in the final result's new column
I am trying to solve as below, but I dont know how to add value d3
from step 3 to b2
in the final result.
select T1.a1,T1.a2,T1.a3,T1.a4,T2.b2 from
T1
left join T2 on T1.a1 == T2.a1 AND T1.a2 == "x"
left join (
select T3.a1,T4.d3 from T3,T4
where T3.c1 == T4.c1 AND T4.d2 == "y")
) joined on joined.a1 == T1.a1 and T1.a2 == "z"
where a4 == "xx";
Sample data
T1 table :
a1 | a2 | a3 | a4 |
1 | x | cat| xx |
2 | aa | hat| la |
3 | z | mat| xx |
T2 table :
b1 | b2 | a1 |
11 | 984 | 1 |
22 | 234 | 2 |
T3 table :
c1 | a1 |
111 | 3 |
222 | 7 |
T4 table :
d1 | d2 | d3 | c1 |
1111 | y | 100 | 111 |
2222 | yy | 200 | 333 |
Expected Result :
a1 | a2 | a3 | a4 | new column
1 | x | cat | xx | 984 (from T2.b2)
3 | z | mat | xx | 100 (from T4.d3)
Please help me to correct my query. Appreciate your help.
Assuming you want no results when your conditions are not met, this query will give you the results you want. It uses a CASE
expression to select the correct value for new column
, using the value of a2
to decide whether to use b2
or d3
for that value. Rows which don't meet your conditions are excluded by the WHERE
clause.
SELECT T1.*,
CASE WHEN T1.a2 = 'x' THEN T2.b2
WHEN T1.a2 = 'z' THEN T4.d3
END AS `new column`
FROM T1
LEFT JOIN T2 ON T2.a1 = T1.a1
LEFT JOIN T3 ON T3.a1 = T1.a1
LEFT JOIN T4 ON T4.c1 = T3.c1
WHERE a4 = 'xx' AND (a2 = 'x' OR a2 = 'z' AND d2 = 'y')
ORDER BY a1
Output:
a1 a2 a3 a4 new column
1 x cat xx 984
3 z mat xx 100
Demo on dbfiddle
You can write this as:
SELECT T1.*,
(CASE WHEN T1.a2 = 'x'
THEN (SELECT T2.b2
FROM T2
WHERE T2.a1 = T1.a1
)
ELSE (SELECT T4.d3
FROM T3 JOIN
T4
ON T4.c1 = T3.c1
WHERE T3.a1 = T1.a1 AND T4.d2 = 'y'
)
END) as new_column
FROM T1
WHERE T1.a4 = 'xx' AND T1.a2 IN ('x', 'z')
ORDER BY T1.a1;
This assumes that at most one row matches from each of the two possibilities.
If so, I find that the logic here better captures your intent.
Service to service authentication in (hapi+molecular) NodeJS
Best practice in sending nodejs socket changes to SQL Server?
Unable to create a Pool instance with constructor in jest test
Cloud Build for Google App Engine - Flexible is failing with a generic 'exit status 1'
I'm trying to look if a record of a table, has at least one record on a pivot table that is used on a many to many relationship
I am trying to get a query to display some resultsI double checked the logic to make sure that it is correct and it looks like it should work to me but does not
I have two tables, words and paragraphThe words table is as below: