MySQL Subquery to SELECT from another table

Question

I'd like to create a query like that looks up a value from another table if the value of a particular field is not 0. Like this?

SELECT id, name, type, site_id
   IF (site_id > 0)
     THEN (SELECT id FROM setups WHERE setups.id = image.site_id) as site
     ELSE "" as site
FROM image
  ORDER BY image.id

Can someone help me with the syntax for doing the SELECT in the middle, only if the value of site_id is not 0 and based on the value of site_id. If the site_id = 0, I need return an empty string.

Thanks!

Gordon Linoff · Accepted Answer · 2015-07-08 02:48:14Z

3

You can do this with a correlated subquery and a case (which I prefer to the if function):

SELECT i.id, i.name, i.type, i.site_id,
       (CASE WHEN i.site_id > 0
            THEN (SELECT s.id FROM setups s WHERE s.id = i.site_id)
            ELSE ''
        END) as site
FROM image i
ORDER BY i.id

edited Jul 8, 2015 at 2:48

answered Jul 7, 2015 at 1:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Barbs Over a year ago

Thanks Gordon. Most helpful, unfortunately I have struck another problem I need to get my head around. I will be back . :)

Jjsg08 · Accepted Answer · 2015-07-07 02:08:02Z

0

if you need the related id from setups i recommend:

SELECT `image`.`id`, `image`.`name`, `image`.`type`, `setups`.`id` as site_id FROM `image`, `setups` WHERE `setups`.`id`=`image`.`site_id`

If site_id is 0 its will be descarted, only the matching rows will be showed, the subqueries are very slow compared whit joins

edited Jul 7, 2015 at 2:08

answered Jul 7, 2015 at 1:57

Jjsg08

12312 bronze badges

Collectives™ on Stack Overflow

MySQL Subquery to SELECT from another table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related