Combine result of two tables
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?
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
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
第二种方法是
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 )
尝试这个
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
下一篇: 结合两个表的结果