Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql query two tables, UNION and where clause

I have two tables.

I query like this:

SELECT * FROM (
   Select requester_name,receiver_name from poem_authors_follow_requests  as one 
UNION 
Select requester_name,receiver_name from poem_authors_friend_requests as two 
) as u 
where (LOWER(requester_name)=LOWER('user1') or LOWER(receiver_name)=LOWER('user1'))

I am using UNION because i want to get distinct values for each user if a user exists in the first table and in the second.

For example:

table1

nameofuser
peter

table2

nameofuser
peter

if peter is on either table i should get the name one time because it exists on both tables.

Still i get one row from first table and a second from table number two. What is wrong?

Any help appreciated.

like image 930
stefanosn Avatar asked Mar 13 '12 22:03

stefanosn


People also ask

Can we use UNION in WHERE clause?

Using the Where Clause With the UNION OperatorWe can use the WHERE clause in either one or both of the SELECT statements to filter out the rows being combined. We can also use the WHERE clause in only one of the SELECT statements in the UNION.


2 Answers

There are two problems with your SQL:

  1. (THis is not the question, but should be considered) by using WHERE over the UNION instead of the tables, you create a performance nightmare: MySQL will create a temporary table containing the UNION, then query it over the WHERE. Using a calculation on a field (LOWER(requester_name)) makes this even worse.

  2. The reason you get two rows is, that UNION DISTINCT will only suppress real duplicates, so the tuple (someuser,peter) and the tuple (someotheruser, peter) will result in duplication.

Edit

To make (someuser, peter) a duplicate of (peter, someuser) you could use:

SELECT
  IF(requester_name='peter', receiver_name, requester_name) AS otheruser
FROM
  ...
UNION
SELECT
  IF(requester_name='peter', receiver_name, requester_name) AS otheruser
FROM
  ...

So you only select someuser which you already know : peter

like image 146
Eugen Rieck Avatar answered Sep 30 '22 19:09

Eugen Rieck


You need the where clause on both selects:

select requester_name, receiver_name
from poem_authors_follow_requests
where LOWER(requester_name) = LOWER('user1') or LOWER(receiver_name) = LOWER('user1')
union
select requester_name, receiver_name
from poem_authors_friend_requests
where LOWER(requester_name) = LOWER('user1') or LOWER(receiver_name) = LOWER('user1')

The two queries are independent of each other, so you shouldn't try to connect them other than by union.

like image 40
Bohemian Avatar answered Sep 30 '22 20:09

Bohemian