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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy