Execute the query on the basis of two columns

Question

This is my table on which I want to execute the query on the basis of two columns.I want to execute the query on the basis of first column values. It must pick the first column value if put in the where clause of the case statement.

id  | Service
-------------
123 | 4
124 | 4
125 | 3
126 | 6
-------------

     Select Service
    case when Service = 4 then (select size from table1 where id = {first column value})
    case when Service = 3 then (select size from table2 where id = {first column value})
    case when Service = 6 then (select size from table3 where id ={first column value})
    End as
    Size 
    From Services_table

You can't use `SELECT *` in a subquery like that (unless the other table has 1 column, but that's very unlikely here). You (probably) want a `LEFT JOIN`, however, queries like this normally scream poor design choices. — Thom A, Feb 07 '19 at 10:25
You should use the join's. Can you post your second table structure. — Mano, Feb 07 '19 at 10:26
What's the problem with the given query? Does it result in any error message? — Nico Haase, Feb 07 '19 at 11:10

score 0 · Answer 1 · answered Feb 07 '19 at 11:40

I think your query will work if you just replace {first column value} with id, like this:

SELECT Service,
    CASE
        WHEN Service = 4 THEN (SELECT Size FROM Sizes1 WHERE Id = Id)
        WHEN Service = 5 THEN (SELECT Size FROM Sizes2 WHERE Id = Id)
        WHEN Service = 6 THEN (SELECT Size FROM Sizes3 WHERE Id = Id)
    END AS Size 
FROM Services_Table

However, JOIN's are (usually) preferable to subqueries, so you could perhaps rewrite this query:

SELECT Service,
    CASE
        WHEN Service = 4 THEN S1.Size
        WHEN Service = 5 THEN S2.Size
        WHEN Service = 6 THEN S3.Size
    END AS Size 
FROM Services_Table S
LEFT JOIN Sizes1 S1 ON S.Id = S1.Id
LEFT JOIN Sizes2 S2 ON S.Id = S2.Id
LEFT JOIN Sizes3 S3 ON S.Id = S3.Id

That being said, I'd re-iterate the comment above that queries like this are usually a sign of questionable design choices and you should consider re-working your data structure.

score 0 · Answer 2 · answered Feb 07 '19 at 12:39

I think you just want a correlated subquery. For this to work you need appropriate qualified column names:

Select Service
       (case when Service = 4
             then (select t1.size from table1 t1 where t1.id = st.id)
             when Service = 3
             then (select t1.size from table1 t2 where t2.id = st.id)
             when Service = 6
             then (select t3.size from table1 t2 where t3.id = st.id)
        end) as Size 
From Services_table st

Execute the query on the basis of two columns

2 Answers2