February 19, 2010 at 7:30 am
CREATE TABLE T1
(
col1 int
)
CREATE TABLE T2
(
col1 int
)
CREATE TABLE T3
(
col1 int,
col2 int NULL,
col3 CHAR(1)
)
insert into T1
select 1
insert into T1
select 2
insert into T1
select 3
insert into T2
select 100
insert into T3
select 100,1,'X'
insert into T3
select 100,NULL,'Y'
I am trying to achieve the result as
T1.col1, T3.col3
1 X
2 Y
3 Y
so I written this query
SELECT T1.col1,T3.col3
FROM T1
JOIN T2 ON 1=1
LEFT JOIN T3 ON (T3.col1 = T2.col1 AND (T1.col1 = T3.col2 OR T3.col2 IS NULL) )
give me 4 records
please help.
February 19, 2010 at 7:46 am
What is with JOIN T2 ON 1=1?
SELECT T1.col1,T3.col3
FROM T1
JOIN T2 ON 1=1
LEFT JOIN T3 ON (T3.col1 = T2.col1 AND (T1.col1 = T3.col2 OR T3.col2 IS NULL) )
GROUP BY T1.col1,T3.col3
How are you going to get a X when you are not inserting the value into the column?
Is this a homework assignment?
If not do you have the initial requirement?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 19, 2010 at 7:51 am
Thanks for your reply, but your GROUP BY query also gives me 4 records, what I want to achieve is get the Y if T3 has col2 as NULL but if col3 has something value then give me X not 2 more records as X and Y both.
No this is not hoework, one of our previous collegues has designed a rubbish design where NULL is used for ALL values.
February 19, 2010 at 7:56 am
What you are trying to do is to join to the NULL record if the other record doesn't exist. You can do this row on row comparision with SQL you have to column on column comparison. TO do what you want to do, you need to join to t3 twice
SELECT T1.col1,isnull(T3.col3, t3_2.col3) col3
FROM T1
cross JOIN T2
LEFT JOIN T3 ON (T3.col1 = T2.col1 AND (T1.col1 = T3.col2 ))
LEFT JOIN T3 t3_2 ON (t3_2.col1 = T2.col1 AND (t3_2.col2 is null OR t3_2.col1 IS NULL) )
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
February 19, 2010 at 8:00 am
Thanks Simon, Thats brilliant answer, but now another complications.
what if I have 6 columns as a combination.
for e.g. T1 has 6 columns more and T3 has 6 nullable columns what do you suggest?
February 19, 2010 at 8:02 am
Should come to something like this.
SELECT T1.col1
, T3.col3
FROM T1
inner join T2
ON 1 = 1 -- I just don't like this construct
inner JOIN T3
ON T3.col1 = T2.col1
AND T3.col2 = T1.col1
union all
SELECT T1.col1
, T3.col3
FROM T1
inner join T2
ON 1 = 1 -- I just don't like this construct
inner JOIN T3
ON T3.col1 = T2.col1
and T3.col2 IS NULL
Where not exists ( select *
from T3 a
left join T2 b
on b.col1 = a.col1
left join T1 c
on c.col1 = a.col2
Where a.col1 = T3.col1
and c.col1 = T1.col1 )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 19, 2010 at 8:33 am
nice work! 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply