CODEWITHSUNDEEP
mysqlunion
Ana
Ana

Reputation: 614

MySQL UNION on same table with two WHERE clause

I have a table alerts 

date | alert | status | sentDate | id

In this table, after I have sent the alert, I set the status to 'sent' and set the sent date. By default the status is 'not' (which means it has not been sent yet).

Now, I want to get the last sentDate, I can get this using the following query

SELECT sentDate FROM alerts WHERE id = someid AND status = 'sent' Order by date desc limit 1;

Also, I want to get the latest alert which has not been sent, I am using this query 

SELECT date, alert FROM alerts WHERE id = someid AND status = 'not' order by date asc limit 1;

Is it possible to do this in one query? perhaps using UNION. I have tried to apply UNION/UNION ALL on these two queries but it gives me error.

Can anyone please help?

Thanks in advance.

Upvotes: 2

Views: 1768

Answers (4)

AL̲̳I
AL̲̳I

Reputation: 2461

You could try this one

    SELECT 
    (Select date from alerts where id= someid AND status='not' 
ORDER by date DESC limit 1) latestNotSentDate, 
    (Select date from alerts where id= someid AND status='sent' 
ORDER BY date DESC limit 1) latestSentDate;

Upvotes: 0

Nazmul Hasan
Nazmul Hasan

Reputation: 1977

You Can Try this :

SELECT MAX(t.sentDate) AS sentDate, MAX(t.date) AS date, MAX(alert) AS alert
FROM
(
    SELECT MAX(sentDate) AS  sentDate, "" AS date, "" AS alert
    FROM alerts 
    WHERE id = someid 
    AND status = 'sent'

    UNION 

    SELECT "" AS sentDate, date, alert 
    FROM alerts 
    WHERE id = someid 
    AND status = 'not'
) t

Upvotes: 1

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

SELECT 
max(CASE WHEN status = 'sent' THEN DATE END) AS sentDate,
max(CASE WHEN status = 'not' THEN DATE END) AS notSentDate 
FROM alerts WHERE id = someid

You can try above query.

It will help you.

Upvotes: 2

Loïc Di Benedetto
Loïc Di Benedetto

Reputation: 897

The number of columns have to be the same in your queries, try this :

SELECT sentDate,'' FROM alerts WHERE id = 'id' AND status = 'sent' Order by date desc limit 1;

UNION

SELECT date, alert FROM alerts WHERE id = 'id' AND status = 'not' order by date asc limit 1;

In addition, i think the status is irrelevant, this information is given by the presence of a sentDate.

I think you can write :

SELECT sentDate,'' FROM alerts WHERE id = 'id' AND sentDate is not null Order by date desc limit 1;

    UNION

    SELECT date, alert FROM alerts WHERE id = 'id' AND sentDate is NULL order by date asc limit 1;

Upvotes: -1

Related Questions