CODEWITHSUNDEEP
mysql
Netorica
Netorica

Reputation: 19327

Using Where Clause in a Union Query

I have a mysql query like this

SELECT id, dpost FROM uid1088_qposts
UNION
SELECT id, dpost FROM uid1091_qposts
WHERE DATE(dpost)='2012-01-01'

but the dpost column is a datetime column which stores a data like this

2012-01-12 11:55:43

the query shows no errors but produces a wrong output

its a query done in phpMyAdmin

is there anyway i can union two tables using a where clause in this kind of filtering? the two tables does not unite... only the first queried table shows its data and filters a wrong date

Upvotes: 1

Views: 21112

Answers (5)

John Woo
John Woo

Reputation: 263693

SELECT AllRecords.`ID`, AllRecords.`dPost`
    (SELECT `ID`, `dPost` FROM uid1088_qposts
        UNION
    SELECT `ID`, `dPost` FROM uid1091_qposts) as AllRecords
FROM AllRecords
WHERE DATE(dpost)='2012-01-01';

Upvotes: 0

JuSchz
JuSchz

Reputation: 1198

In your case your code is interpreted like :

(SELECT id, dpost FROM uid1088_qposts)
UNION
(SELECT id, dpost FROM uid1091_qposts WHERE DATE(dpost)='2012-01-01')

if you want to filter all of your lines use

(SELECT id, dpost FROM uid1088_qposts WHERE DATE(dpost)='2012-01-01')
UNION
(SELECT id, dpost FROM uid1091_qposts WHERE DATE(dpost)='2012-01-01')

Upvotes: 3

Tadeck
Tadeck

Reputation: 137310

If you want to limit the results from both tables, then put WHERE clause in both queries.

UNION just appends the results from one query to the results of another one.

Upvotes: 2

Devart
Devart

Reputation: 121902

In this case you can use a subquery -

SELECT * FROM (
  SELECT id, dpost FROM uid1088_qposts
    UNION
  SELECT id, dpost FROM uid1091_qposts
  ) t
WHERE DATE(dpost)='2012-01-01';

Upvotes: 9

Jordan Running
Jordan Running

Reputation: 106027

The WHERE clause in your code only applies to the second query in the union, not both. To apply it to both you'll need to use it with both:

SELECT id, dpost FROM uid1088_qposts
  WHERE DATE(dpost) = '2012-01-01'
UNION
SELECT id, dpost FROM uid1091_qposts
  WHERE DATE(dpost) = '2012-01-01'

Upvotes: 1

Related Questions