CODEWITHSUNDEEP
mysql
Reno
Reno

Reputation: 2982

Select a field only if no other records with the same field value meet a condition

If the query below returns a 'template' id for any template assigned to a report that has not yet started...

How can I also say (AND for each one of these template id's - this template id can't be on any other report that has already started).

Or, I mean I'm trying to finish out the query below so it says "Give me the id's of all templates where the template is not in use by any report that has started". Like if for each t.id I looped through the table making sure that there were no other reports with the template_id (t.id) that have already started.

SELECT DISTINCT t.id, 
FROM templates t
LEFT OUTER JOIN reports r ON r.template_id = t.id
WHERE r.start_time >=  UTC_TIMESTAMP()

Thanks!

Upvotes: 0

Views: 77

Answers (1)

shreyas
shreyas

Reputation: 208

You can use subquery like 

SELECT DISTINCT t.id, 
FROM templates t
LEFT OUTER JOIN reports r ON r.template_id = t.id
WHERE r.start_time >=  UTC_TIMESTAMP() and t.id not in (select CONCAT_WS(',',tid) where report started)

i am not completing the subquery as i dont know exact table structure. so complete the where clause accordingly

Upvotes: 1

Related Questions