SQL Query: Merging Tables

[Jim]

This SQL query has been driving me nuts, I am sure there is a simple
way of doing this but I am afraid it is eluding me at the moment.

I have two tables:

Table1
X Y
----------- -----------
1 12
2 41
6 14
9 12

Table2
X Y
----------- -----------
1 10
2 12
3 12
5 12
6 32

What I am after is a list of the maximum Y values for each value of X
from the tables:

X Y
----------- -----------
1 12
2 41
3 12
5 12
6 32
9 12

Any help would be greatly appreciated

Thanks - Jim

[David Portas]

Please always include DDL with future posts otherwise we are forced to
guess what keys, constraints, nullability, etc are supposed to apply to
the problem. If we have to guess then you might not get the right
answer or the best answer. You should also find you get good answers
quicker if you include sample data as INSERT statements rather than as
lists - that way others can test out possible solutions with your
actual data.

Let me make an assumption that your tables look like this:

CREATE TABLE Table1 (x INTEGER NOT NULL PRIMARY KEY, y INTEGER NOT
NULL)

CREATE TABLE Table2 (x INTEGER NOT NULL PRIMARY KEY, y INTEGER NOT
NULL)

X is a key and the columns are not nullable.

Try this:

SELECT COALESCE(T1.x,T 2.x),
CASE WHEN T1.y > T2.y
THEN T1.y
ELSE COALESCE(T2.y,T 1.y)
END
FROM Table1 AS T1
FULL JOIN Table2 AS T2
ON T1.x = T2.x

If X is not unique, then try one of these:

SELECT x, MAX(y)
FROM
(SELECT x,y FROM Table1 UNION ALL
SELECT x,y FROM Table2) AS T
GROUP BY x

SELECT COALESCE(T1.x,T 2.x),
CASE WHEN T1.y > T2.y
THEN T1.y
ELSE COALESCE(T2.y,T 1.y)
END
FROM
(SELECT x,MAX(y)AS y
FROM Table1
GROUP BY x) AS T1
FULL JOIN
(SELECT x,MAX(y) AS y
FROM Table2
GROUP BY x) AS T2
ON T1.x = T2.x

Hope this helps.

--
David Portas
SQL Server MVP
--

[Ross Presser]

On 17 Jun 2005 02:27:30 -0700, David Portas wrote:

Try this:

SELECT COALESCE(T1.x,T 2.x),
CASE WHEN T1.y > T2.y
THEN T1.y
ELSE COALESCE(T2.y,T 1.y)
END
FROM Table1 AS T1
FULL JOIN Table2 AS T2
ON T1.x = T2.x

If X is not unique, then try one of these:

SELECT x, MAX(y)
FROM
(SELECT x,y FROM Table1 UNION ALL
SELECT x,y FROM Table2) AS T
GROUP BY x

David,

May I ask why you would choose to go the COALESCE + FULL JOIN route, rather
than just the MAX + GROUP BY? It would seem much more straightforward , and
it seems to me it would work even if X is a unique key. Is there some
advantage to FULL JOIN that I don't understand?

[Erland Sommarskog]

Ross Presser (rp******@NOSPA Mgmail.com.inva lid) writes:

May I ask why you would choose to go the COALESCE + FULL JOIN route,
rather than just the MAX + GROUP BY? It would seem much more
straightforward , and it seems to me it would work even if X is a unique
key. Is there some advantage to FULL JOIN that I don't understand?

Because there were two tables, and the example included values that
were in 1) Table1 only 2) in Table2 only 3) in both tables. You must
somehow combine these two tables. If X is a unique key, FULL JOIN is
a possible way to go.

Personally my favourite is this the UNION query:

SELECT x, MAX(y)
FROM
(SELECT x,y FROM Table1 UNION ALL
SELECT x,y FROM Table2) AS T
GROUP BY x

It expresses the operation well, it's easy to understand, and you
can easily extended to several tables if that need would appear. And it
does not make any assumptions about the uniqueness of X.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jim]

Apologies for the lack of DDL and insert statements, I was in a hurry
and annoyed at myself for not getting it to work so forgot.

Thank you for your input and solutions, I had gone down the route of
the:

SELECT x, MAX(y)
FROM
(SELECT x,y FROM Table1 UNION ALL
SELECT x,y FROM Table2) AS T
GROUP BY x

But had failed to get it to work correctly, it has been way too long
since I last used SQL in anger too much front end coding.

Also interesting to see the coalesce solutions.

Thanks again - Jim

[Ross Presser]

On Fri, 17 Jun 2005 21:56:29 +0000 (UTC), Erland Sommarskog wrote:

It expresses the operation well, it's easy to understand, and you
can easily extended to several tables if that need would appear. And it
does not make any assumptions about the uniqueness of X.

That's exactly the point I was making ... why worry about whether or not X
is unique? It wasn't part of the problem statement.