Skip to content
Advertisement

SQL query – fill missing value

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement