mysql query two tables, UNION and where clause

Question

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.

Are the columns in both of your tables of the same time? Maybe in one table the column is of type `char`and in the other it's of type `varchar`there might me some spaces.... — Bernhard Döbler, Mar 13 '12 at 22:28
In your where statement, you probably want to reference the "u" before each field... so `where (LOWER(u.requester_name) = ...` this is simlar to the answer you can see in: http://stackoverflow.com/questions/5452233/where-statement-after-a-union-in-sql — James Oravec, Apr 07 '14 at 22:15

score 14 · Accepted Answer · edited Nov 06 '13 at 12:26

There are two problems with your SQL:

(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.
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

Thanks for telling me Eugen Rieck you were right it finds duplicates as you said...could you please tell me if there is a way to solve this? (someuser,peter) or (peter,someuser) should be a duplicate in my case. — stefanosn, Mar 13 '12 at 22:57

score 2 · Answer 2 · answered Mar 13 '12 at 22:29

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.

score 1 · Answer 3 · answered Nov 18 '13 at 05:48

You can use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. UNION is available as of MySQL 4.0. This section illustrates how to use it. Suppose you have two tables that list prospective and actual customers, a third that lists vendors from whom you purchase supplies, and you want to create a single mailing list by merging names and addresses from all three tables. UNION provides a way to do this. Assume the three tables have the following contents:

http://w3webtutorial.blogspot.com/2013/11/union-in-mysql.html

score 0 · Answer 4 · edited May 23 '17 at 12:09

0

In your where statement, reference the alias "u" for each field refence in your where statement.

So the beginning of your where statement would be like: where (LOWER(u.requester_name) = ...

This is simlar to the answer you can see in: WHERE statement after a UNION in SQL?

edited May 23 '17 at 12:09

Community

1
1

answered Apr 07 '14 at 22:16

James Oravec

19,579
27
94
160

score 0 · Answer 5 · answered Mar 13 '12 at 22:30

You are doing the union before and then applying the where clause. So you would get a unique combination of "requester_name,receiver_name" and then the where clause would apply. Apply the where clause in each select...

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'))

score -1 · Answer 6 · edited Jul 19 '12 at 07:50

-1

You should be able to use the INTERSECT keyword instead of doing a nested query on a UNION.

SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b

can simply be rewritten to

SELECT a.member_id, a.name
FROM a INNER JOIN b
USING (member_id, name)

http://www.bitbybit.dk/carsten/blog/?p=71

edited Jul 19 '12 at 07:50

Taz

3,718
2
37
59

answered Mar 13 '12 at 22:29

Jun Wei Lee

1,002
11
25

MySQL does not support the `INTERSECT` keyword! – onedaywhen Mar 14 '12 at 08:29

mysql query two tables, UNION and where clause

6 Answers6

Linked