mysql query two tables, UNION and where clause
Solution 1
There are two problems with your SQL:
(THis is not the question, but should be considered) by using
WHERE
over theUNION
instead of the tables, you create a performance nightmare: MySQL will create a temporary table containing theUNION
, then query it over theWHERE
. 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
Solution 2
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
.
Solution 3
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
Comments
-
stefanosn almost 2 years
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.
-
stefanosn over 12 yearsThanks 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.
-
onedaywhen over 12 yearsMySQL does not support the
INTERSECT
keyword!