SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Complex Select		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xhostx
Constraint Violating Yak Guru

USA
261 Posts
Posted - 01/31/2013 :  13:10:09  Show Profile  Reply with Quote
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
Edited by - xhostx on 01/31/2013 13:17:36

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 01/31/2013 :  13:26:40  Show Profile  Reply with Quote 

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

MatiTuk
Starting Member

3 Posts
Posted - 01/31/2013 :  13:43:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1499 Posts
Posted - 01/31/2013 :  13:55:01  Show Profile  Reply with Quote
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

India
47069 Posts
Posted - 01/31/2013 :  13:56:17  Show Profile  Reply with Quote
Thanks James

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
261 Posts
Posted - 01/31/2013 :  14:57:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5469 Posts
Posted - 01/31/2013 :  15:44:26  Show Profile  Reply with Quote
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

USA
261 Posts
Posted - 01/31/2013 :  20:22:48  Show Profile  Reply with Quote
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

India
47069 Posts
Posted - 02/01/2013 :  02:37:18  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000