SQL SELECT CASE WITH SELECT

Question

I have this situation on my DB MySQL:

TABLE_A
Cod | Value1 | Value2 |Value3

TABLE_B
Cod | Value4 | Value5

TABLE_C
Cod | Value6

And I use this query:

SELECT
  table_a.value1 AS Number, 
  table_a.value2 AS Date, 
  table_b.name   AS Name, 
  table_b.age    AS Age 
FROM 
  table_a 
  LEFT JOIN table_c 
         ON table_a.cod = table_c.cod 
            AND table_c.value5 = 1 
  LEFT JOIN table_b 
         ON table_c.cod = table_b.cod 
WHERE value3 = 'MY_SEARCH_VALUE' 
ORDER BY table_a.cod ASC

All works great, but now I must select elements from TABLE_A (join with TABLE_B and TABLE_C) connect with one element on TABLE_A.

For example when TABLE_A.Cod = 'X' then select also TABLE_A.Cod ='Y' and TABLE_A.Cod ='Z' on the contrary when TABLE_A.Cod = 'ALL_OTHERS_CODE' use the query above.

Edit: (Added from OP's comments below):

The results of my query

SELECT
  TABLE_A.Value1 AS Number,
  TABLE_A.Value2 AS Date,
  TABLE_B.name AS Name,
  TABLE_B.age AS Age
FROM
  TABLE_A
  LEFT JOIN TABLE_C
         ON TABLE_A.Cod = TABLE_C.Cod
            AND TABLE_C.Value5 =1
  LEFT JOIN TABLE_B
         ON TABLE_C.Cod = TABLE_B.Cod
WHERE Value3 = 'DEFENDER'
ORDER BY TABLE_A.Cod ASC

is for example: (12 - 14/01/01 - John - 18)

I want the result to be more like the one above. If I should try another code for example 'DEFENDER_A' and I expect to have: (12, 14/01/01, John, 18 --- 5, 14/01/01, Frank, 19)

Your requirements are unclear. Please use sample data and example results to demonstrate your requirements and any known edge cases. — MatBailie
The results of my query (SELECT TABLE_A.Value1 AS Number, TABLE_A.Value2 AS Date, TABLE_B.name AS Name, TABLE_B.age AS Age FROM TABLE_A LEFT JOIN TABLE_C ON TABLE_A.Cod = TABLE_C.Cod AND TABLE_C.Value5 =1 LEFT JOIN TABLE_B ON TABLE_C.Cod = TABLE_B.Cod WHERE Value3 = 'DEFENDER' ORDER BY TABLE_A.Cod ASC is for example: (12 - 14/01/01 - John - 18) — user3078845
I want the result to be more like the one above if I should try another code for example 'DEFENDER_A' and I expect to have: (12,14/01/01,John,18 --- 5,14/01/01,Frank,19) — user3078845
The requirements are vague. Please provide sample date so thatwould be helpful to provide solutions — Thangamani Palanisamy
I wanna connect select of one element in TABLE_A with other in the same table and the result have to show the 4 field. — user3078845

IshaS IshaS · Accepted Answer · 2014-02-17T09:35:27

change your code like this.

Select
TABLE_A.Value1 AS Number, 
TABLE_A.Value2 AS Date, 
TABLE_B.name AS Name, 
TABLE_B.age AS Age 
FROM TABLE_A LEFT JOIN TABLE_C ON TABLE_A.Cod = TABLE_C.Cod 
AND TABLE_C.Value5 =1 LEFT JOIN TABLE_B ON TABLE_A.Cod = TABLE_B.Cod 
WHERE Value3 = 'DEFENDER' 
ORDER BY TABLE_A.Cod ASC

SQL SELECT CASE WITH SELECT

1 Answers