How to select all from SQL query including subquery

Question

I want to perform a mySQL query that returns all the results in a table but has an additional column that has values "selected" if they correspond to a separate query. For example

| name |
|------|
| a    |
| b    |
| c    |

I want to be able to return the following when I do a query "select * from table where name = 'a';

| name | selected |
|------|----------|
| a    |  yes     |
| b    |          |
| c    |          |

That is, I also want to know which ones where left out.

score 2 · Answer 1 · answered May 20 '11 at 23:46

2

select *
    , selected = case
        when exists (
            select *
            from table2 t2
            where t2.field = t1.field
                and ...etc.
        ) then 1 
        else 0
    end
from table t1

answered May 20 '11 at 23:46

Dan Roberts

4,664
3
34
43

score 1 · Answer 2 · answered May 20 '11 at 23:42

1

Where clause restricts your row set so you get only rows with name 'a'. To get all of them either join the limited table back to itself or use IF without where:

SELECT *, IF( name='a', 'yes', '') AS selected
FROM table

answered May 20 '11 at 23:42

piotrm

12,038
4
31
28

+1 for giving the best answer for the SQL given, however the OP's actual queries may be more complicated – Conrad Frix May 21 '11 at 01:20

score 0 · Accepted Answer · answered May 23 '11 at 16:09

0

The one that worked for me was:

select a.*
    , name = case
        when 
           a.name = 'a'
         then 0 
        else 1
    end
as selected
from points as a

answered May 23 '11 at 16:09

Rio

14,182
21
67
107

How to select all from SQL query including subquery

3 Answers3