How to join two tables in mysql via SELECT

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 con开发者_StackOverflowtains 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.

---

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 )

---

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.

---

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

---

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 )