ignore last one, this is the tsql 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 ------------------ ID Desc ------------------ 1 Glasses 2 Red Hair 3 Blue Eyes ----------- Table Y ----------- Person ID ----------- 856 1 856 3 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 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 ------------------ ID Desc ------------------ 1 Glasses 2 Red Hair 3 Blue Eyes ----------- Table Y ----------- Person ID ----------- 856 1 856 3 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 to 'False'. Thank You in advance for your help!
-
hehe, I guess it does look like an assignment I would have had. Actually it's a huge complex query that I simplified as far as I could for the sake of getting a response. I still need to translate the case statement into the full version, then I'll see how well it goes. Thanks!
-
Still can't quite get it. Here's more detail. tTesterUsage Table ------------------ TesterID | UsageID ------------------ 856 | 97 856 | 98 900 | 97 900 | 102 tLuUsage Table -------------------- UsageID | UsageIndex -------------------- 97 | 60001 98 | 60002 99 | 60003 100 | 60004 101 | 60005 102 | 60006 tUConsumer Table --------------------------- CMCode | GeneralDescription --------------------------- 60001 | Reads Books 60002 | Reads Magazines 60003 | Reads Newspaper 60004 | Watches TV 60005 | Rents Movies 60006 | Movie Theatre ****************************************** * tLuUsage.UsageIndex = tUConsumer.CMCode* ****************************************** Desired Result Below when TesterID = '856': ----------------------------- GeneralDescription | There ----------------------------- Reads Books | True Reads Magazines | True Reads NewsPaper | False Watches TV | False Rents Movies | False Movie Theatre | False I've tried this below, still doesn't quite do it. CREATE PROCEDURE usp_tp_GetConsUsage @TesterID char(7) AS declare @Out table (There varchar(5), TesterID char(7)) insert into @Out SELECT tUConsumer.CMCode as There, tTesterUsage.TesterID FROM tTesterUsage inner JOIN tLuUsage ON tTesterUsage.UsageID = tLuUsage.UsageID inner JOIN tUConsumer ON tLuUsage.UsageIndex = tUConsumer.CMCode where tTesterUsage.TesterID = @TesterID SELECT distinct tUConsumer.GeneralDescription, case when Gonz.There is null then 'False' else 'True' end as There FROM tLuUsage inner JOIN tUConsumer ON tLuUsage.UsageIndex = tUConsumer.CMCode inner join tTesterUsage on tLuUsage.UsageID = tTesterUsage.UsageID left join @Out Gonz on tTesterUsage.TesterID = Gonz.TesterID GO
-
Still can't quite get it. Here's more detail. tTesterUsage Table ------------------ TesterID | UsageID ------------------ 856 | 97 856 | 98 900 | 97 900 | 102 tLuUsage Table -------------------- UsageID | UsageIndex -------------------- 97 | 60001 98 | 60002 99 | 60003 100 | 60004 101 | 60005 102 | 60006 tUConsumer Table --------------------------- CMCode | GeneralDescription --------------------------- 60001 | Reads Books 60002 | Reads Magazines 60003 | Reads Newspaper 60004 | Watches TV 60005 | Rents Movies 60006 | Movie Theatre ****************************************** * tLuUsage.UsageIndex = tUConsumer.CMCode* ****************************************** Desired Result Below when TesterID = '856': ----------------------------- GeneralDescription | There ----------------------------- Reads Books | True Reads Magazines | True Reads NewsPaper | False Watches TV | False Rents Movies | False Movie Theatre | False I've tried this below, still doesn't quite do it. CREATE PROCEDURE usp_tp_GetConsUsage @TesterID char(7) AS declare @Out table (There varchar(5), TesterID char(7)) insert into @Out SELECT tUConsumer.CMCode as There, tTesterUsage.TesterID FROM tTesterUsage inner JOIN tLuUsage ON tTesterUsage.UsageID = tLuUsage.UsageID inner JOIN tUConsumer ON tLuUsage.UsageIndex = tUConsumer.CMCode where tTesterUsage.TesterID = @TesterID SELECT distinct tUConsumer.GeneralDescription, case when Gonz.There is null then 'False' else 'True' end as There FROM tLuUsage inner JOIN tUConsumer ON tLuUsage.UsageIndex = tUConsumer.CMCode inner join tTesterUsage on tLuUsage.UsageID = tTesterUsage.UsageID left join @Out Gonz on tTesterUsage.TesterID = Gonz.TesterID GO
tUConsumerneeds to be the main table o the query. Something like this:SELECT distinct c.GeneralDescription,
case when l.UsageId is null then 'False' else 'True' end as There
FROM tUConsumer c
LEFT OUTER JOIN tLuUsage l ON c.CMCode = l.UsageIndex
INNER JOIN tTesterUsage t ON l.UsageId = t.UsageId
where t.TesterId = @TesterId
-
tUConsumerneeds to be the main table o the query. Something like this:SELECT distinct c.GeneralDescription,
case when l.UsageId is null then 'False' else 'True' end as There
FROM tUConsumer c
LEFT OUTER JOIN tLuUsage l ON c.CMCode = l.UsageIndex
INNER JOIN tTesterUsage t ON l.UsageId = t.UsageId
where t.TesterId = @TesterId
When I try it that way it only displays the GeneralDescriptions that apply to that TesterID. In other words, it only displays what is True. I need all the possible GeneralDescriptions displayed, with either False or True next to the description. Thank You Again for all of your help
-
When I try it that way it only displays the GeneralDescriptions that apply to that TesterID. In other words, it only displays what is True. I need all the possible GeneralDescriptions displayed, with either False or True next to the description. Thank You Again for all of your help
-
tUConsumerneeds to be the main table o the query. Something like this:SELECT distinct c.GeneralDescription,
case when l.UsageId is null then 'False' else 'True' end as There
FROM tUConsumer c
LEFT OUTER JOIN tLuUsage l ON c.CMCode = l.UsageIndex
INNER JOIN tTesterUsage t ON l.UsageId = t.UsageId
where t.TesterId = @TesterId
Hope the following query would help select distinct UC.GeneralDescription GeneralDescription, case when TU.TesterID is null then 'False' else 'True' end As There from tUConsumer UC inner join tLuUsage LU on UC.CMCode = LU.UsageIndex left join tTesterUsage TU on TU.UsageID = LU.UsageID and TU.TesterID = @testerID