Report on having two subgroup tables

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)

Report on having two subgroup tables

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2007-09-06 : 23:56:08

Dear All,
I would like to make a report which contains:

<Product> <hasSupport> <hasWarranty>
aaaa Yes Yes
BBB Yes No

from three tables:

Product:
ID
Name

SupportLog: (one to many)
SLogID
ProductID:
SupportDate:

WarrantyLog: (one to many)
WLogID
ProductID:
WarrantDate:

Can I make this report using one SQL?

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 02:37:06

[code]
SELECT [Product] = P.ID,
[hasSupport] = CASE WHEN EXISTS (SELECT * FROM SupportLog AS SL WHERE SL.ProductID = P.ID) THEN 'Yes' ELSE 'No' END,
[hasWarranty] = CASE WHEN EXISTS (SELECT * FROM WarrantyLog AS WL WHERE WL.ProductID = P.ID) THEN 'Yes' ELSE 'No' END
FROM Product
[/code]
Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources