CODEWITHSUNDEEP
sqlmysql
jitendra
jitendra

Reputation: 1458

Using a single common WHERE condition for UNION in SQL

I am trying to do something like this: 

SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a JOIN Location b ON a.id=b.id  
UNION  
SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a JOIN Location b ON a.id=b.id  
WHERE a.date>'2010-01-01'  
ORDER BY EnrollDate

But the WHERE condition applies to the second SELECT statement only. I need to somehow apply to both the SELECT. The only option I have now is to apply WHERE condition individually. But I am working with several UNIONs and it is kind of tedious to include WHERE in all the places. I was wondering if there is an easy way out.

By the way, I am working with MySQL.

Upvotes: 15

Views: 24405

Answers (3)

nsmyself
nsmyself

Reputation: 3565

Have you tried something like:

SELECT * FROM (
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a 
    JOIN Location b ON a.id = b.id  
    UNION  
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a 
    JOIN Location b ON a.id = b.id 
) A
WHERE a.date > '2010-01-01'  
ORDER BY EnrollDate

Upvotes: 5

Janick Bernet
Janick Bernet

Reputation: 21184

SELECT * FROM (
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a 
    JOIN Location b ON a.id=b.id  
    UNION  
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a 
    JOIN Location b ON a.id=b.id  
) A
WHERE EnrollDate > '2010-01-01'  
ORDER BY EnrollDate

This also has the advantage, compared to individual ORDER BY's that the whole result is correctly ordered.

Upvotes: 19

Frank
Frank

Reputation: 2648

There is no way around it, you have to repeat the WHERE for each individual select clause.

Upvotes: -2

Related Questions