SQL query for joining four tables

29
April 16, 2019, at 01:10 AM

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 :

  1. I want to have all columns from T1 when a4 == "xx"

  2. If T1.a1 == T2.a1 and T1.a2 == "x", then I want b2 to be included in the final result as new column.

  3. If T3.c1 == T4.c1 and T4.d2 == "y", then take value from d3 which needs to be joined to step4

  4. 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.

Answer 1

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

Answer 2

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.

READ ALSO
Versioning on a table with foreign keys

Versioning on a table with foreign keys

Let's suppose I have 3 tables,

41
MySQL count all records that have at least one record on a pivot table

MySQL count all records that have at least one record on a pivot table

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

34
table inner join not displaying expected results

table inner join not displaying expected results

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

19
Combining results from two tables into JSON data

Combining results from two tables into JSON data

I have two tables, words and paragraphThe words table is as below:

29