SQL query - fill missing value

Question

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

For the missing X rows, there are multiple other records with matching IDs. Which records should be use for the replacement values? — Tim Biegeleisen, Nov 26 '20 at 08:47
Do I understand correctly that PrimaryKey is made by two columns: id & place? — Brambor, Nov 26 '20 at 08:51
@TimBiegeleisen I would like to use SQL query to select everything for place 70 and the added type data for the given ID. For example: for ID = 1, type X5 (which is in position 10) will be added in position 70. One piece = one ID and the piece goes through the places and they write the data to the table. Only place 70 does not fill in the "type". So, for example, ID 1 is the same piece in place of 10.50 and just 70. — Cesc, Nov 26 '20 at 08:54
I recomment changing your question: to columns where id=1, set type=X1 to columns where id=2, set type=X2 to columns where id=3, set type=X3 — Brambor, Nov 26 '20 at 10:01

Brambor · Accepted Answer · 2020-11-26T11:20:55.237

1

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

edited Nov 26 '20 at 11:20

answered Nov 26 '20 at 09:04

Brambor

604
1
8
25

Thank you, but thats not exactly it. Because in table can be ID 4 with type X4 and again on place 10,50,70. And again on place 70 will be type 'null' but this type is X4 not X5 – Cesc Nov 26 '20 at 09:10
Do you have a second table where there is "if (id, place) is not defined place (type = ..)" where .. is one of X1, X2... ? – Brambor Nov 26 '20 at 09:13
Otherwise, I am not getting where am I supposed to get the information for the "then X5" part of code... – Brambor Nov 26 '20 at 09:13
There can be more than one type in the table. Each ID has a type. The type is added in each place for the given ID. It is not replenished only on place70. ID 1 will have the same type everywhere. ID 2 will also have the same type everywhere. It doesn't matter what type it is, but simply ID 1 has X5 in place of 10 and 50 and 70. Only in 70 is this type not noted in the table. But there can be, for example, ID 55 with type X99 and this type will also be in place of 10 and 50 and 70. – Cesc Nov 26 '20 at 09:26
Ok, I get it like this: `type` is always identified by `id` (pair `(id, type)` is always the same, given any `id`). So if we find `id` whose type is not `null`, it is the `type` we are looking for. When we find a row without `type`. Correct? – Brambor Nov 26 '20 at 09:32
1

Yes, this is it :) – Cesc Nov 26 '20 at 09:34
@Cesc I fixed my answer according to your specifications :) – Brambor Nov 26 '20 at 09:52
Thanks, but I tried to build a demo on mysql: [link](https://www.db-fiddle.com/f/eNHUyjHheUwRDCVSgjmUFF/1) and it still doesn't work. – Cesc Nov 26 '20 at 10:49
@Cesc see the updated answer if my demo works like you would like it to. – Brambor Nov 26 '20 at 10:55
Your demo works well and exactly as I want. But when I use sql-query, an error occurs. – Cesc Nov 26 '20 at 10:59
[Try](https://www.db-fiddle.com/f/eNHUyjHheUwRDCVSgjmUFF/2) a syntax error will appear. – Cesc Nov 26 '20 at 11:03
Replace ` is ` with ` = `. `IS` is only for `IS [NOT] NULL`. – Thorsten Kettner Nov 26 '20 at 11:09
@Cesc I added MySQL solution as well ;) – Brambor Nov 26 '20 at 11:21

score 1 · Answer 2 · answered Nov 26 '20 at 13:39

1

I would strongly recommend window functions. For instance:

select t.*,
       max(type) over (partition by id) as imputed_typ
from t;

To "fill in" the existing value:

select id, place,
       coalesce(type, max(type) over (partition by id)) as type
from t;

Not only are these queries much simpler than a subquery, but they should have better performance.

answered Nov 26 '20 at 13:39

Gordon Linoff

1,242,037
58
646
786

I agree it is cleaner, but SQL understands what is asked of it and then makes it's own way of getting the data, I think. – Brambor Nov 27 '20 at 21:48
@Brambor . . . I'm not sure what you intend with your comment. In all databases that I know of, the window functions generate more efficient execution plans than equivalent other methods. – Gordon Linoff Nov 27 '20 at 22:24

SQL query - fill missing value

2 Answers2