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/

Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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/

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

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

Go to Top of Page
   

- Advertisement -