MySQL SELECT with conditionally NULL column

Question

I'm brand new to SQL programming, and having a bit of trouble. I have the following MySQL script:

SELECT
    MyPrimaryTable.PrimaryKey, Alias1.String AS Output1,
    CASE
        WHEN Id2 IS NOT NULL THEN Alias2.String
        ELSE ""
    END AS Output2
FROM MyPrimaryTable, MySecondaryTable AS Alias1, MySecondaryTable AS Alias2
WHERE
    MyPrimaryTable.Id1 = Alias1.Id1
    AND ((MyPrimaryTable.Id2 IS NULL) OR (MyPrimaryTable.Id2 = Alias2.Id2));

Basically, I have a primary table that contains two IDs, Id1 and Id2, and I'm trying to associate each one of these IDs with a string in a secondary table and display these strings in the SELECT result. However, the second ID in the primary table is allowed to be NULL. The output I would like, in this case, is the first string as expected and the second string as blank (""). The script I wrote does this, except it adds a new row for each possible string, even though all the rows look the same.

Expected results with possible strings Foo1, Foo2, Foo3, and Foo4, with two entries in the table:

PK   Output1 Output2
1    Foo1    Foo4
2    Foo2

What I get:

1    Foo1    Foo4
2    Foo2
2    Foo2
2    Foo2

How can I clean this up and make it work as expected? Thanks.

EDIT: I was hoping not to use DISTINCT because I want to use this in a VIEW, and DISTINCT will make the view non-updatable.

Have you considered group_concat() ? dev.mysql.com/doc/refman/5.0/en/… — Nicola Cossu
– Nicola Cossu, Commented May 2, 2011 at 8:23

DanielB · Accepted Answer · 2011-05-02 08:46:04Z

1

Try grouping with

GROUP BY MyPrimaryTable.PrimaryKey

on the other end, you may join the tables not with WHERE

SELECT
    MyPrimaryTable.PrimaryKey, 
    Alias1.String AS Output1,
    CASE
        WHEN Id2 IS NOT NULL THEN Alias2.String
        ELSE ""
    END AS Output2
FROM MyPrimaryTable, 
    LEFT JOIN MySecondaryTable AS Alias1 ON MyPrimaryTable.Id1 = Alias1.Id1
    LEFT JOIN MySecondaryTable AS Alias2 ON MyPrimaryTable.Id2 = Alias2.Id2

answered May 2, 2011 at 8:46

DanielB

20.3k2 gold badges48 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RawwrBag Over a year ago

Thank you! LEFT JOIN was just what I was looking for.

RawwrBag Over a year ago

Unfortunately this doesn't seem to update correctly when used in a VIEW, IE if I try UPDATE Output1 SET String = "test" WHERE PrimaryKey = 1234 it seems to replace all instances of the original string with "test", not just where the PK is 1234. Off the top of your head do you know why this might be?

RawwrBag Over a year ago

Nevermind, ignore that last comment, I'm dumb -- I'd have to edit the original number, not the associated string. Durp.

Collectives™ on Stack Overflow

MySQL SELECT with conditionally NULL column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related