I have a table:
id type place 1 X1 10 2 X2 10 3 X3 10 1 X1 50 2 X2 50 3 X3 50 1 null 70 2 null 70 3 null 70
I have three parts with id (1,2,3) and these parts are of any type in different places. However, on the place, “70” hasn’t written type. However, as part of piece counting, I need to count for location 70 for a given type (for example, X5). But the one in place 70 is not listed. How can I add this data in my selection within a subquery? For example, I take those ids and somehow refer that those ids are of type X5?
I want:
SQL query to select all columns for location 70 with the type added for the given id.
I tried this sql query, but it is wrong:
SELECT id, type, place FROM TABLE t1 WHERE type = ( SELECT type FROM TABLE t2 WHERE t1.id = t2.id AND place = 10 ) AND place = 70
Advertisement
Answer
I found part of answer to your question here:
SELECT id, CASE WHEN type is null then ( SELECT type FROM T as t2 WHERE t1.id is t2.id and t2.type is not null) else type end as type, place FROM T as t1
my demo (I made it here):
id type place 1 X1 10 2 X2 10 3 null 70 2 null 70 1 null 70 3 X3 50 3 X3 50 2 X2 50 1 X1 50 1 X1 50 1 X1 50 3 X3 10
returns
id type place 1 X1 10 2 X2 10 3 X3 70 2 X2 70 1 X1 70 3 X3 50 3 X3 50 2 X2 50 1 X1 50 1 X1 50 1 X1 50 3 X3 10
MySQL solution (demo):
SELECT id, CASE WHEN type is null then ( SELECT type FROM T as t2 WHERE (t1.id = t2.id) and (t2.type is not null) LIMIT 1) else type end as type, place FROM T as t1