LEFT JOIN question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

LEFT JOIN question

Author

Topic

rpoojari
Starting Member

6 Posts

Posted - 2008-10-17 : 07:13:04

Hi,
I have a question on whether there is better way to write the following query.
lets say I have two tables like this with exactly the same columns and the second table has a foreign key pointing to the first table.

T1: Id1 int primary key identity(1,1) not null, col1, col2, col3, col4, col5

and another table like this
T2: Id2 int primary key identity(1,1) not null, Id1, col1, col2, col3, col4, col5

and I want to select all rows from T1, but where T2 exists for the same Id, select data from T2

In other words

SELECT (CASE WHEN T2.Id2 IS NOT NULL THEN T2.Col1 ELSE T1.Col1 END) AS Col1
, (CASE WHEN T2.Id2 IS NOT NULL THEN T2.Col2 ELSE T1.Col2 END) AS Col2
, (CASE WHEN T2.Id2 IS NOT NULL THEN T2.Col3 ELSE T1.Col3 END) AS Col3
, (CASE WHEN T2.Id2 IS NOT NULL THEN T2.Col4 ELSE T1.Col4 END) AS Col4
, (CASE WHEN T2.Id2 IS NOT NULL THEN T2.Col5 ELSE T1.Col5 END) AS Col5
FROM T1
LEFT JOIN T2 ON T1.Id1 = T2.Id1

Is there a better way to write this query instead of writing a case for every single column and WITHOUT using a union operator? I can't use ISNULL because some of these columns are nullable.
and I am not sure how this query will perform with large amount of data. I have few more tables like this keep different versions of data but the columns are same. and if I wrap this in a view and join it with similar views, it should perform well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 08:19:57

[code]SELECT COALESCE(T2.Col1,T1.Col1)AS Col1,
COALESCE(T2.Col2,T1.Col2)AS Col2,
COALESCE(T2.Col3,T1.Col3)AS Col3,
COALESCE(T2.Col4,T1.Col4)AS Col4,
COALESCE(T2.Col5,T1.Col5)AS Col5
FROM T1
LEFT JOIN T2 ON T1.Id1 = T2.Id1[/code]

Jawad Khan
Starting Member

21 Posts

Posted - 2008-10-17 : 09:07:34

visakh16! I think you have misinterpreted the query. The NULL checking is for T2.ID2.
In your case you have checked for NULL for every column. This will be problem in case let us say, the col1 is NULL in T2, although other columns (t2.col2, t2.col3.....) have values. So the query output will get one value from T1 and other values from T2.
I think the requirement is that if the ID2 is not null, then every column of T2 will be outputted, even if they are null.

To me the query by rpoojari is just fine.

rpoojari
Starting Member

6 Posts

Posted - 2008-10-17 : 09:16:12

yes, that is right. there are some nullable columns, so I can't use COALESCE or ISNULL on every column. it seems there is no other or clean way to write this kind of queries.
thanks for your replies.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 09:41:15

ok so do you mean this?

SELECT *
FROM
(
SELECT ID,col1, col2, col3, col4, col5,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Cat) AS Seq
FROM
(
SELECT ID,col1, col2, col3, col4, col5,1 AS Cat
FROM T2
UNION ALL
SELECT ID,col1, col2, col3, col4, col5,2
FROM T1
)t
)r
WHERE Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources