November 1, 2006 at 5:57 am
Hi,
I need to join two tables but I only want a 0 or 1 rows from the second table, if the 2nd table has multiple rows this needs to be row where one of the columns is the maximum value.
Example:-
Table 1 has a key column of say T1c1. Table 2 has a key column of T2c1 and a integer column T2c2. If table 2 has no records where the two keys match I want just details from table 1 with null in the table 2 columns (standard left join behaviour), but if table 2 has three records where the keys match I only want the table 2 row values from the table records where column T2c2 is the maximum i.e. if T2c2 has values of 1, 2 and 3 I want only the row data from where T2c2 = 3.
Thanks,
Paul.
November 1, 2006 at 6:22 am
You could use a sub query:
TABLE x (
a int
)
TABLE y (
a int,
b int
)
SELECT a, b
FROM x
INNER JOIN (
SELECT TOP 1 a,b
FROM y
ORDER BY b DESC
) AS z
ON x.a = z.a
or
SELECT a,
(SELECT TOP 1 a,b
FROM y
WHERE y.a = x.a
ORDER BY b DESC) AS b
FROM x
I have no idea which is better for you, there are other ways as well using max instead of top 1 which also may be more performant for you.
November 1, 2006 at 6:25 am
Paul,
I hope this might help you.....
CREATE TABLE Table1( T1C1 INT NOT NULL PRIMARY KEY CLUSTERED )
GO
CREATE TABLE Table2( T2C1 INT NOT NULL REFERENCES Table1 (T1C1) , T2C2 INT NOT NULL )
GO
INSERT INTO Table1( T1C1 )
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
GO
INSERT INTO Table2( T2C1 , T2C2 )
SELECT 1 , 1
UNION ALL
SELECT 2 , 1
UNION ALL
SELECT 2 , 2
UNION ALL
SELECT 3 , 1
UNION ALL
SELECT 3 , 2
UNION ALL
SELECT 3 , 3
UNION ALL
SELECT 4 , 1
UNION ALL
SELECT 4 , 2
UNION ALL
SELECT 4 , 3
UNION ALL
SELECT 4 , 4
GO
--SELECT * FROM Table1
--SELECT * FROM Table2
SELECT * FROM
Table1 T1 LEFT OUTER JOIN ( SELECT T2C1 , MAX(T2C2) AS T2C2 FROM Table2 GROUP BY T2C1 ) T2 ON T1.T1C1 = T2.T2C1
GO
DROP TABLE Table2
GO
DROP TABLE Table1
GO
--Ramesh
November 1, 2006 at 6:27 am
Ah good point, my inner join should have been a left join
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply