i tried to get all the productid within a package (and apackage itself is also a product) with the following query.

WITH RecursiveTable_CTE(ProductID, PackageID)

AS

(

SELECT pp.ProductID, pp.PackageID FROM ProductPackage pp

UNION...

Thanks Dan.

Thank you very much i appreciate your support.:)

Go it now .

Below is the query i just tweaked it more.

with PG as (Select Distinct OD.OrderID, P.ProductGroup

from OrderDetail OD join Product p on P.ProductID = OD.ProductID

where P.Productgroup...

I want to show on report where any body ordered

CFA &FRM Ordered Together that means they can order any number on this combination.

CAIA & CFA Ordered Together that means they...

Results of Above query:

SELECT '39495','PREL1','CFA Book','PREL1' UNION ALL

SELECT '39496','1','Flashcards (Palm Pilot Version)','CFA' UNION ALL

SELECT '39497','2','Flashcards (Palm Pilot Version)','CFA' UNION ALL

SELECT '39498','3','Flashcards (Palm Pilot Version)','CFA' UNION ALL

SELECT '39499','1','Flashcards (Paper Version)','CFA' UNION...

Table Name : Products And Orderdetail

PRODUCTID PRODUCTGROUP LEVELS

1 ...

Table Name : Products And Orders

PRODUCTID PRODUCTGROUP LEVELS

1 ...

Requirement: I need a query to report where if any one ordered more than one product group it should show me on report but have exception if

CFA &FRM Ordered Together...

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else...

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else...

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else...

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else...

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else...

Datatype for no email is bit in customer table i wnat to include this in a case statement.

SELECT

CASE

WHEN NoEMail NOT IN

(select emailaddress from Reports.dbo.OptOutEmailAddresses)

AND

substring

(Customer.Email, (CHARINDEX('@',...