how to use where condition in both parts of union in mysql

Question

I'm using php and mysql for making a chat application. I want to use a where condition in both parts of union but this not working my code is :

$Query = "(SELECT u.userid, u.photo, u.username FROM `last_activity` AS la INNER JOIN user AS u ON la.userid = u.userid LEFT join private_chat pc ON u.userid = pc.sender_id WHERE la.last_activity_date BETWEEN '2020-09-21 10:20:00' AND '2020-09-21 10:30:00' AND u.username like '%amit%' ORDER BY pc.created_date DESC) UNION (SELECT userid, photo, username FROM user u LEFT join private_chat pc ON u.userid = pc.sender_id WHERE u.u_type = '2')";

and i want like and this not works

$Query = "(SELECT u.userid, u.photo, u.username FROM `last_activity` AS la INNER JOIN user AS u ON la.userid = u.userid LEFT join private_chat pc ON u.userid = pc.sender_id) UNION (SELECT userid, photo, username FROM user u LEFT join private_chat pc ON u.userid = pc.sender_id ) WHERE la.last_activity_date BETWEEN '2020-09-21 10:20:00' AND '2020-09-21 10:30:00' AND u.u_type = '2' AND u.username like '%amit%' ORDER BY pc.created_date DESC";

what is (($UsersVal !="") ? " , I think you have a syntax error in your code — Karim Samir, Sep 21 '20 at 12:34
There are a lot of issues with your `UNION` statement in second clause. Please refer to this [MySQL UNION](https://dev.mysql.com/doc/refman/5.7/en/union.html) and search for `ORDER BY and LIMIT in Unions` this section and look for second example — Dark Knight, Sep 21 '20 at 12:38
a common query after `UNION` is highly discouraged as it will pick all records first and then filter and also will not be able to use `index` — Dark Knight, Sep 21 '20 at 12:39
Does this answer your question? [WHERE statement after a UNION in SQL?](https://stackoverflow.com/questions/5452233/where-statement-after-a-union-in-sql) — Dark Knight, Sep 21 '20 at 12:40
@KarimSamir - this query mixed in php and mysql... now i changed my question — Ashu Kumar Gupta, Sep 21 '20 at 12:57

score 0 · Answer 1 · edited Sep 21 '20 at 14:17

0

SELECT *
FROM 
(
    SELECT u.userid, u.photo, u.username, pc.created_date
    FROM `last_activity` AS la
    INNER JOIN `user` AS u ON la.userid = u.userid
    LEFT JOIN private_chat pc ON u.userid = pc.sender_id
    WHERE la.last_activity_date BETWEEN '2020-09-21 10:20:00' AND '2020-09-21 10:30:00'
        AND u.username LIKE '%amit%' 
        
    UNION ALL
    
    SELECT userid, photo, username, pc.created_date
    FROM `user` u
    LEFT JOIN private_chat pc ON u.userid = pc.sender_id
    WHERE u.u_type = '2'
) a
ORDER BY a.created_date DESC;

edited Sep 21 '20 at 14:17

Dark Knight

6,116
1
15
37

answered Sep 21 '20 at 13:41

Aditya Sawant

193
1
3

You can't use order by clause above UNION ALL statement. – Aditya Sawant Sep 21 '20 at 13:42
UNION ALL concats two resultsets. – Aditya Sawant Sep 21 '20 at 13:43
refer : https://www.w3schools.com/sql/sql_ref_union.asp – Aditya Sawant Sep 21 '20 at 13:44
please mention why he can't use `last_activity_date` in outer query. What is issue in inner select statement and all. So OP can know, what he was doing wrong. BTW your query is actual solution for this. – Dark Knight Sep 21 '20 at 14:15
`last_activity_date` column belongs to `last_activity` table which is in first select query. – Aditya Sawant Sep 21 '20 at 14:56

how to use where condition in both parts of union in mysql

1 Answers1