Multiple Case Statements - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple Case Statements

Author

Topic

solutus
Starting Member

2 Posts

Posted - 2008-03-14 : 19:10:57

This query brings back 2 rows for each record, one for each case statement. How can I change this to bring in the contsupp.notes for both types of data? Or could it be fixed in the joins?

select
contact1.accountno,
contact1.key5 as "Ch#",
contact2.uexpsort as "Sort",
contact1.company as "Church",
contact1.contact as "Editor",
contact1.phone1 as "Phone",
contact2.uemerg as "Emergency",
contact1.Address1 as "Address",
contact1.city as "City",
contact1.state as "State",
contact1.zip as "Zip",
contact2.ubulletnqt as "Qty",
contact2.ubullcolor as "BullColor",
contact2.ubarcode as "Barcode",
contact2.udelivery as "Delivery",
contact2.uoutputdev as "OutputDev",
contact2.utimedeliv as "Time",
contact2.ucolor as "DelivColor",
contact2.ucollated as "Collated",
contact2.ustapled as "Stapled",
case when contsupp.contsupref = 'Delivery Notes' then contsupp.notes end as "Deliverynotes",
case when contsupp.contsupref = 'Cover Change Slip' then contsupp.notes end as "CoverChangeSlip"

from
Contact1 inner join contact2 on Contact1.Accountno = Contact2.Accountno
inner join contsupp on Contact1.Accountno=Contsupp.Accountno
where
contact1.key5 not like '' and contsupp.contsupref='Delivery Notes' or contsupp.contsupref ='Cover Change Slip' order by contact1.key5

Thanks for your help

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-03-15 : 06:57:38

Do this via your JOIN statment .Create a derived table

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

solutus
Starting Member

2 Posts

Posted - 2008-03-17 : 14:40:28

That worked. Thanks for pointing this novice in the right direction:
select
contact1.accountno,
contact1.key5 as "Ch#",
contact2.uexpsort as "Sort",
contact1.company as "Church",
contact1.contact as "Editor",
contact1.phone1 as "Phone",
contact2.uemerg as "Emergency",
contact1.Address1 as "Address",
contact1.city as "City",
contact1.state as "State",
contact1.zip as "Zip",
contact2.ubulletnqt as "Qty",
contact2.ubullcolor as "BullColor",
contact2.ubarcode as "Barcode",
contact2.udelivery as "Delivery",
contact2.uoutputdev as "OutputDev",
contact2.utimedeliv as "Time",
contact2.ucolor as "DelivColor",
contact2.ucollated as "Collated",
contact2.ustapled as "Stapled",
dnotes.notes as "DN",
ccsnotes.notes as "CCS"
FROM CONTACT1 INNER JOIN CONTACT2 ON CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO
inner join (select accountno, notes from contsupp where contsupp.contsupref='Delivery Notes')AS DNOTES ON CONTACT1.ACCOUNTNO=dnotes.ACCOUNTNO
inner join (select accountno, notes from contsupp where contsupp.contsupref='Cover Change Slip')AS CCSNOTES ON CONTACT1.ACCOUNTNO=ccsnotes.ACCOUNTNO
where contact1.key5<>''
order by contact1.key5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources