How to join two tables in mysql via SELECT

Question

So I have two tables. Both have five columns in common (roomname, usercount, userlimit, topic, extra), and at the same time each table has other columns as well that are unrelated.

What I want is to use SELECT and retrieve a combination of TableA + TableB where the 'roomname' value must be unique in the result.

So if TableA contains a row with roomname='room1' and TableB contains a roomname='room1', take preference to the one in TableA, and do not add the item from TableB. Keep in mind that only the 'roomname' value will be the same. usercount,userlimit,topic,extra will be different from TableA to TableB even if the 'roomname' value is the same.

Are you asking to remove duplicates from one of the tables in question or just from the result set returned? — Joe Stefanelli, Jul 06 '11 at 21:40
What determines a duplicate that should be removed vs retained? — OMG Ponies, Jul 06 '11 at 21:41
I'm not trying to modify the the tables, I meant join them in a set when using SELECT. — Josh, Jul 06 '11 at 21:46
@Josh FYI: The confusion that you have noticed in the answers is caused by the fact that you didn't post any sample data or results. This means people have to guess at what is going on. I hope you find an answer, though! — Chris Cunningham, Jul 06 '11 at 22:05
Ok, I have edited my original post and I think it better explains it. — Josh, Jul 06 '11 at 22:55

score 1 · Accepted Answer · answered Jul 06 '11 at 23:36

1

SELECT roomname, usercount, userlimit, topic, extra
FROM TableA

UNION ALL

SELECT roomname, usercount, userlimit, topic, extra
FROM TableB
WHERE roomname NOT IN
    ( SELECT roomname FROM TableA )

answered Jul 06 '11 at 23:36

ypercubeᵀᴹ

113,259
19
174
235

That is exactly what I wanted. Thanks. Next time I will just explain myself better so others can understand. I feel bad because Marc did help a lot :( – Josh Jul 06 '11 at 23:47
@Josh: You can vote Marc's answer as helpful (and tick it as best). I was just lucky to notice this question after you updated it. Next time it would be better to add a few rows of the tables (and the expected output) besides the explanation. It's usually easier for anyone else to understand what you want that way. – ypercubeᵀᴹ Jul 06 '11 at 23:54

score 0 · Answer 2 · edited Jul 06 '11 at 23:55

0

SELECT roomname, usercount, userlimit, topic, extra
FROM TableA

UNION

SELECT roomname, usercount, userlimit, topic, extra
FROM TableA

By default, a union query in MySQL does 'distinct' and eliminates duplicate rows (aka UNION DISTINCT). If you wanted to get the duplicate rows, then you'd do UNION ALL instead.

ok, since you've now said that only the roomname is common/duplicated between the tables, how about something like:

select tableA.*, tableB.*
from tableA
join tableB on tableA.roomname = tableB.roomname
where (tableA.usercount <> tableB.usercount) or (tableA.userlimit <> tableB.userlimit)
   or (tableA.topic <> tableB.topic) or (tableA.extra <> tableB.extra)

That'd bring back all rows from both tables where they share the same roomname, but have differences in one or more of the other fields. If you want to show only exact duplicates on those fields, change the <> to =.

mod part 2

select tableB.*
from tableB
left join tableA on tableA.roomname = tableB.roomname
where tableA.roomname is null

this will return all records from table B where the roomname field does NOT have a match in table A

After OP's updated explanation:

SELECT roomname, usercount, userlimit, topic, extra
FROM TableA

UNION ALL

SELECT roomname, usercount, userlimit, topic, extra
FROM TableB
WHERE roomname NOT IN
    ( SELECT roomname FROM TableA )

edited Jul 06 '11 at 23:55

ypercubeᵀᴹ

113,259
19
174
235

answered Jul 06 '11 at 21:41

Marc B

356,200
43
426
500

I tried that before posting here. And it seems to give me duplicate results anyways. I get multiple rows with the same "roomname" value for some reason. – Josh Jul 06 '11 at 21:45
UNION defines distinctness as identity across all columns. Do you have multiple records for a single roomname with different values in any of the other four columns? Do you not want those? – Dan J Jul 06 '11 at 21:54
Could always wrap the union query in another query and have that outer query do the distinctness filtering. – Marc B Jul 06 '11 at 21:56
Yes, the values of usercount,userlimit,topic,extra are different in TableB than they are in TableA. But the roomname value is the same. Is there some way to filter out duplicates solely based on comparing the roomname values? – Josh Jul 06 '11 at 21:58
Ah, you didn't say that only roomname was common across both tables. I'll mod my answer. – Marc B Jul 06 '11 at 21:59
Sorry, first time using this lol. That's very close, except I want pretty much the opposite of what that does. I want the result to be the combined result of TableA and TableB, but not add duplicates (based on the roomname value) to the result if TableA already contains an item with that value. – Josh Jul 06 '11 at 22:13
ok. so basically pull out everything from tableB, but only those tableB records which do not have a roomname-matching record in tableA? In other words, if "room 3" is listed in tableA, don't show any 'room 3' records from tableB? – Marc B Jul 06 '11 at 22:14
That is correct. Except I also want to include the entire TableA. – Josh Jul 06 '11 at 22:18

score 0 · Answer 3 · answered Jul 06 '11 at 21:42

0

What you're trying to do sounds more like a UNION than a JOIN. The former simply gives you a combined resultset, whereas the latter performs an SQL JOIN, which modifies the resultset.

To combine the result of two queries, UNION the SELECT queries, such as

(SELECT roomname, usercount, userlimit, topic, extra
    FROM TableA)
UNION DISTINCT
(SELECT roomname, usercount, userlimit, topic, extra
    FROM TableB);

UNION DISTINCT eliminates the duplicates. I don't have a server on-hand to test the syntax, but accoridng to the manual, it should be correct.

answered Jul 06 '11 at 21:42

Naltharial

2,132
14
21

I have tried this, and even with union distinct it's returning the result with multiple rows containing the same 'roomname' value. Does the fact that one of the tables has extra columns make a difference even though we are only selecting those five? – Josh Jul 06 '11 at 21:49
DISTINCT will only remove rows which are fully equal. If you're trying to get a distinct list of roomnames, try selecting only that column. You can also GROUP BY roomname. – Naltharial Jul 06 '11 at 21:55
Since the other fields values are different, this solution is not good. – Itay Moav -Malimovka Jul 06 '11 at 23:47

score 0 · Answer 4 · edited May 23 '17 at 11:48

SELECT roomname, usercount, userlimit, topic, extra
FROM
     (
(SELECT roomname, usercount, userlimit, topic, extra
    FROM TableA)
UNION
(SELECT roomname, usercount, userlimit, topic, extra
    FROM TableB)
) AAA
GROUP BY roomname

Do notice I do not use any aggregated functions, this will make the query show only the first result from each group (which, in this case, suppose to come from the first SELECT in the UNION.

Also @ypercube solution's good (and simpler to understand than mine). But due to a bug in MySQl, I think my solution is more efficient (you need to check this).

How to join two tables in mysql via SELECT

4 Answers4