tsql query puzzle
-
I need to create a query where I pass it 'Person' and I create a column called 'There'. I'm using SQL Server 2000. ------------------ ----------- Table X Table Y ------------------ ----------- ID Desc Person ID ------------------ ----------- 1 Glasses 856 1 2 Red Hair 856 3 3 Blue Eyes 900 1 900 2 900 3 ------------------------------- Needed Result when I pass '856' ------------------------------- Desc There ----------------------- Glasses True Red Hair False Blue Eyes True This means that I must always output every possible 'Desc' and set 'There' to 'True' when we have a match otherwise set it to 'False'. Thank You in advance for your help!
-
I need to create a query where I pass it 'Person' and I create a column called 'There'. I'm using SQL Server 2000. ------------------ ----------- Table X Table Y ------------------ ----------- ID Desc Person ID ------------------ ----------- 1 Glasses 856 1 2 Red Hair 856 3 3 Blue Eyes 900 1 900 2 900 3 ------------------------------- Needed Result when I pass '856' ------------------------------- Desc There ----------------------- Glasses True Red Hair False Blue Eyes True This means that I must always output every possible 'Desc' and set 'There' to 'True' when we have a match otherwise set it to 'False'. Thank You in advance for your help!
Try something like:
--Create temp table containing all possible descriptions. select distinct Desc into #temp01 from YourTable --Use outer join to pull list of all descriptions, then cross-match --with person attributes. If the person does not have a matching --attribute then the YT.Desc value will be null. The "case" --statement outputs true/false appropriately. select T1.Desc, case when YT.Desc is null then 'True' else false end There from #temp01 T1 left outer join YourTable YT on YT.PersonId = 856 and YT.Desc = T1.Desc order by 1You can do this without using the temporary table if you wanted (just replace #temp01 in the second query with "(select distinct Desc from YourTable)" to create an in-line view. Andy