Complex Select - 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

Complex Select

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2013-01-31 : 13:10:09

i have T1 (table1)
--


Column 0|Column 1
Iff9   	 	43410
Iff9   	 	43490
S0013S	 	9045561 
S90014A	 	8q2540 
S91214D	 	Vqq9

---
I have T2 (Table2)


----
Field1|Field2
S91214D    S91214D
S91214D    Iff9 
S0013S     null
null       Iff9 
Iff9       Iff9

---
I have this but it doesnt show what I need to:

select 
Field1,
Field2,
case when Field1=column0 then column1 Replacement_Field1,
case when Field2=column0 then column1 Replacement_Field2
 
from T1, T2
where Field1=column0 or Field2=column0 or (Field1=column0 and Field2=column0)

Basically (Field1 and Field2) have the same values as Column0 in table1
Desired result is:


Field1     |Field2  |Replacement_Field1|Replacement_Field2
S91214D    S91214D   Vqq9                Vqq9
S91214D    Iff9      Vqq9                43410,43490
S0013S     null      9045561             no match
null       Iff9      no match            43410,43490
Iff9       Iff9      43410,43490         43410,43490

Thanks for the help.

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 13:26:40

[code]
SELECT t2.Field1,
t2.Field2,
STUFF((SELECT ',' + Column1 FROM table1 WHERE Column0 = t2.Field1 FOR XML PATH()),1,1,'') AS ReplacementField1,
STUFF((SELECT ',' + Column1 FROM table1 WHERE Column0 = t2.Field2 FOR XML PATH()),1,1,'') AS ReplacementField2
FROM Table2 t2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MatiTuk
Starting Member

3 Posts

Posted - 2013-01-31 : 13:43:36

Try something like this.

Select T2.Field1,
T2.Field2,
CASE when (T2.Field1 = T1.Column0) Then ISNULL(T1.Column1,'')
ELSE '' as Replacement_Field1,
CASE when (T2.Field2 = T1.Column0) Then ISNULL(T1.Column1,'')
ELSE '' as Replacement_Field2
from Table1 T1
inner join Table2 T2 on T1.Field1 = T2.Column0
where T1.Field1 = T1.Column0
or T1.Field2 = T1.Column0

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 13:55:01

quote:
Originally posted by visakh16

SELECT t2.Field1,
t2.Field2,
STUFF((SELECT ',' + Column1 FROM table1 WHERE Column0 = t2.Field1 FOR XML PATH('')),1,1,'') AS ReplacementField1,
STUFF((SELECT ',' + Column1 FROM table1 WHERE Column0 = t2.Field2 FOR XML PATH('')),1,1,'') AS ReplacementField2
FROM Table2 t2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Fixing a typo.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 13:56:17

Thanks James

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2013-01-31 : 14:57:03

Thanks! Visakh :)
There something wrong with the STUFF() function.
It gives me an error of parenthesis.!!
Any idea why please?

--------------------------
Joins are what RDBMS's do for a living

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-01-31 : 15:44:26

did you use Jame's corrected-for-typo version?

Be One with the Optimizer
TG

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2013-01-31 : 20:22:48

Its all set guys,

Thank you ALL for the support.

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-01 : 02:37:18

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources