Combine result of two tables

1
votes

I have two tables 

TABLE_A
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| bid   | int(10) unsigned | NO   | PRI | 0       |       |
| uid   | int(10) unsigned | NO   | PRI | 0       |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

and

TABLE_B
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| bid   | int(10) unsigned | NO   | PRI | 0       |       |
| uid   | int(10) unsigned | NO   | PRI | 0       |       |
+-------+------------------+------+-----+---------+-------+

I want to select bid from both tables when the uid = 123; Note: each table has about 15 results and some exists in both tables, I need to select distinctively. so I tried this:

SELECT DISTINCT ta.bid, 
                tb.bid 
FROM   table_a AS ta 
       JOIN table_b AS tb using (uid) 
WHERE  uid = 123;

And I got the wrong answer obviously. Why is it getting 150+ results instead of 30?

6
mysqlsqlselectdistinctunion
UNION? Failing that, consider providing proper DDLs and/or an sqlfiddle TOGETHER WITH THE DESIRED RESULT SET - Strawberry
If you want to get results from both tables that match uid = 123 ? - curiousguy
Oh yeah totally forgot union :) please post it as the answer so I can accept it - saadlulu
@DibyenduDutta Yes thats what I want - saadlulu

6 Answers

2
votes

Try this

SELECT DISTINCT bid FROM TABLE_A WHERE uid = 123
UNION 
SELECT DISTINCT bid FROM TABLE_B WHERE uid = 123

OR

SELECT DISTINCT bid 
FROM (SELECT bid FROM TABLE_A WHERE uid = 123
      UNION 
      SELECT bid FROM TABLE_B WHERE uid = 123
     ) AS A
2
votes
SELECT ta.bid, 
       tb.bid 
FROM   table_a AS ta, 
       table_b AS tb 
WHERE  ta.uid = tb.uid 
       AND ta.uid = 123 
GROUP  BY ta.bid, 
          tb.bid

Second Method would be

SELECT ta.bid, 
       tb.bid 
FROM   table_a AS ta 
       INNER JOIN table_b AS tb 
               ON ( ta.uid = tb.uid ) 
                  AND ( ta.uid = 123 )
1
votes

Try This

select tb1.bid, tb2.bid from TABLE_A  AS tb1  , TABLE_B  AS tb2 
where tb1.bid = tb2.bid 
AND tb1.bid = 123
group by tb1.bid
1
votes

You need to do a union, filtering the remaining results

SELECT bid 
FROM   table_a 
WHERE  uid = 123 
UNION 
SELECT bid 
FROM   table_b 
WHERE  uid = 123

Here's a fiddle to help you visualize the result: http://sqlfiddle.com/#!2/27eea5/4

1
votes

USE UNION Its Used to Combines the result of two or more SELECT statements.

SELECT  bid FROM TABLE_A WHERE uid = 123
UNION 
SELECT  bid FROM TABLE_B WHERE uid = 123
0
votes

try this..

SELECT DISTINCT bid 
FROM   (SELECT bid 
        FROM   table_a 
        WHERE  uid = 123 
        UNION 
        SELECT bid 
        FROM   table_b 
        WHERE  uid = 123) AS temp;