Removing duplicate results while using UNION SELECT

Question

Ive got a problem with a MySQL query.

Let's say, we have two tables:

id qty

1......1

2......1

3......1

4......3

and

id qty

1......2

2......1

6......1

7......2

What I actually need to get is a result filtered by id, so there are no duplicate id's. If they are two same id's, only id whith a higher qty number will be considered. So in and ideal situation I would get this result:

id qty

1......2

2......1

3......1

4......3

6......1

7......2

What Ive tried so far is a UNION SELECT, which works fine, but when it comes across a duplicate ID it wont remove duplicates, as it removes just duplicates rows. GROUP BY didnt help either. I could still sort this out through an array in PHP, but I would love having this sorted at MySQL level if possible.

Thanks everyone for you help, it's much appreciated!

Answer 1

GROUP BY didnt help either

Really? Did you try like this?

SELECT id, MAX(qty) AS qty
FROM
(
    SELECT id, qty FROM table1
    UNION ALL
    SELECT id, qty FROM table2
) T1
GROUP BY id

Answer 2

You can use UNION DISTINCT. Maybe it runs...

SELECT * FROM t1 WHERE ...

UNION DISTINCT

SELECT * FROM t2 WHERE ...