MySQL to return NULL if there is no result

Question

I have this type of query

SELECT one, two, three FROM (
  ( SELECT data AS one FROM thetable WHERE `id` = '$id' AND name = 'one') t1,
  ( SELECT data AS two FROM thetable WHERE `id` = '$id' AND name = 'two') t2,  
  ( SELECT data AS three FROM thetable WHERE `id` = '$id' AND name = 'three') t3
)

and when one of the row is missing for a certain id, it just skips all of the selects related to the id.

I'm hoping to get something like this:

id:24     one:somedata      two:somedata      three:somedata
id:25     one:somedata      two:EMPTY         three:somedata
id:26     one:somedata      two:somedata      three:somedata
id:27     one:EMPTY         two:somedata      three:somedata

but I'm getting:

id:24     one:somedata      two:somedata      three:somedata
id:25     one:EMPTY         two:EMPTY         three:EMPTY
id:26     one:somedata      two:somedata      three:somedata
id:27     one:EMPTY         two:EMPTY         three:EMPTY

Why?

Please use proper JOIN. It's been around for over 25 years!!! — Eric
– Eric, Commented Jul 3, 2020 at 18:13

Fahmi · Accepted Answer · 2020-07-03 17:47:07Z

2

You can try the below way -

select id,
max(case when  name = 'one' then data end) as one,
max(case when  name = 'two' then data end) as two,
max(case when  name = 'three' then data end) as three
from thetable where `id` = '$id'
group by id

edited Jul 3, 2020 at 17:47

answered Jul 3, 2020 at 17:36

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

3 Comments

slaakso Over a year ago

You probably want to move the condition id = '$id' to the WHERE-clause so the query can use an index on id.

Fahmi Over a year ago

@slaakso, yes you are right, thank you for pointing it out

Steven Over a year ago

Wow, thank you! It works, and it works 10 times faster! Why is that? It takes 3 seconds to finish the query, whereas my query was taking 30 seconds to complete.

Collectives™ on Stack Overflow

MySQL to return NULL if there is no result

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