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