show the output in one table - 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

General SQL Server Forums

New to SQL Server Programming

show the output in one table

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2011-01-20 : 12:47:21

i need a query for this logic..i have 3 tables

Tbl1

ID KeyID Name Address
1 101 Chris 101..
2 102 Jay 229..
3 103 shan 3129..
4 104 Dan 432..
5 105 tom 546
6 106 Pim 565...
7 107 Sam 839..
.
.
.

second table is Tbl2

ID LoginID State
1 001 NULL
2 002 FL
3 003 CA
4 NULL NULL
7 NULL NULL
.
.
.

Third table is tbl3
KEYID LoginID State
105 005 TX
106 NULL NULL
107 007 MD
.
.
.

Logic is first i need to check whether loginID and state are there in tbl2 with the tbl1 using ID column and does not exists in tbl2 then i need to check in tbl3 with tbl1 using keyID column and the output i need in one table like this

tbl4

ID HasLoginID State
1 Y NULL
2 Y FL
3 Y CA
4 N NULL
5 Y TX
6 N NULL
7 Y MD
.
.
.
.
Can i get query for this logic...thanks in advance

dev

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-20 : 12:54:27

select t1.id, hasloginid = case when coalesce(t2.loginid,t3.loginid) is not null then 'Y' else 'N' end, State = coalesce(t2.state,t3.state)
from tbl1 t1
left join tbl2 t2
on t1.id = t2.id
left join tbl3 t3
on t1.keyid = t3.keyid

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2011-01-20 : 16:11:30

THIS IS GOOD BUT THE PROBLEM IS

I HAVE DUPLICATE RECORDS IN THE TABLE..THIS IS THE EXAMPLE OF THE OUTPUT I AM GETTING

ID HASLOGINID STATE
1 Y NULL
1 Y TX
2 Y CA
2 Y NULL
3 N NULL
4 Y VA
5 N NULL
6 Y NM
6 Y NULL
7 Y NULL

I NEED THE OUTPUT LIKE THIS

ID HASLOGINID STATE
1 Y TX
2 Y CA
3 N NULL
4 Y VA
5 N NULL
6 Y NM
7 Y NULL

HOW TO FILTER LIKE THIS FROM THAT OUPUT

CAN YOU PLEASE HELP ME IN THIS..THANKS IN ADVANCE

quote:
Originally posted by nigelrivett

select t1.id, hasloginid = case when coalesce(t2.loginid,t3.loginid) is not null then 'Y' else 'N' end, State = coalesce(t2.state,t3.state)
from tbl1 t1
left join tbl2 t2
on t1.id = t2.id
left join tbl3 t3
on t1.keyid = t3.keyid

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dev

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-21 : 00:23:05

If you dont need the Null Values append a check to the above mentioned query:

Where (tbl2.STATE is not null and tbl3.STATE is not null)

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2011-01-21 : 08:07:14

I need the NULL values also but the condition is i have show atleast one ID which is having the "Y" and "State" and having IDs with only "Y" and State is "NULL" and having IDs HasKeyID = NULL and State = NULL.

If you see the sample output results that i posted previously you will get an idea how i need the output.

can any one help me on this

quote:
Originally posted by MIK_2008

If you dont need the Null Values append a check to the above mentioned query:

Where (tbl2.STATE is not null and tbl3.STATE is not null)

dev

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources