Use subquery in mysql

Question

The query below gives me 2 out of the 3 answers I'm looking for. On the sub-query select I get null instead of no

the 3 possible values for column name isCyl could be blank, yes, no

I'm not sure if the sub-query is the best way to go about it, but I don't know how else to re-state the query.

The schedule table has a series of columns to show what tasks must be completed on an assignment. Related tables store the results of the tasks if they were assigned to be completed. So I need to test if a specific task was scheduled. If so, then I need to see if the results of the task have been recorded in the related table. For brevity I am only showing one of the columns here.

SELECT s.`reckey`,  
if(s.cylinders="T",
        (select 
            if(c.areckey is not null, 
                  "yes",
                  "no"
            ) 
            from cylinders c where c.areckey = s.reckey limit 1 
        )
        ,""
) as isCyl 
from schedule s 
where s.assignmentDate between 20161015 and 20161016 
order by s.reckey

Subquery is likely returning no result (null) when no match found. — Uueerdo
– Uueerdo, Commented Oct 20, 2016 at 19:08
Pls provide some sample data, expected outcomes based on the sample data, and the output provided by your query based on the sample data. You probably need a left join and not a subquery. — Shadow
– Shadow, Commented Oct 20, 2016 at 19:11
I also prefer LEFT JOIN, but you could put the IF() around the subquery instead of around the the column name. — Barmar
– Barmar, Commented Oct 20, 2016 at 19:26

Barmar · Accepted Answer · 2016-10-20 19:30:08Z

1

Use a LEFT JOIN, which returns NULL for columns in the child table when there's no match.

SELECT s.reckey, IF(s.cylinders = "T",
                    IF(c.areckey IS NOT NULL, 'yes', 'no'),
                    "") AS isCyl
FROM schedule AS s
LEFT JOIN cylinders AS c ON c.areckey = s.reckey
WHERE s.assignmentDate between 20161015 and 20161016 
ORDER BY s.reckey

If there can be multiple rows in cylinders with the same areckey, change it to:

LEFT JOIN (select distinct areckey FROM cylinders) AS c on c.areckey = s.reckey

or use SELECT DISTINCT in the main query.

answered Oct 20, 2016 at 19:30

Barmar

789k57 gold badges554 silver badges668 bronze badges

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

1 Comment

Claus Over a year ago

Thanks for the clear and concise example. The little add on with the Left Join (select distinct.... was just what I needed. I can have multiple records in the sub table. My result set in this case should be 27 rows. Without your add on suggestion my result set was 67 rows. So I learned an extra thing and I appreciate that.

Collectives™ on Stack Overflow

Use subquery in mysql

1 Answer 1

1 Comment

Hot Network Questions