HELP: Building SQL Query

[cinewest]

My situation is as follows:
I have two tables: InvoiceHeader and InvoiceItemList

InvoiceHeader provides me with InvoiceNumber
InvoiceItemList provides me with ProductID, Description and Qty

I am trying to run two different queries that will answer following questions:

1. How many products were sold were ProductID=X and ProductID=Y on a given invoice for a given date range? In other words, for the date range provided, how many ProductID=X were sold were ProductID=Y also existed?

2. How many products were sold were ProductID=X existed on an invoice but ProductID=Y did not exist.

Ultimately, I need to be able to ascertain how many times ProductID=X was sold when ProductID=Y was present and how many times it was sold without ProductID=Y.

I can do a simple query that shows me how many times X or Y were sold, but not how many times X was sold when Y was present vs when Y is not present.

Hope this makes sense. Thank you for your assistance.

[cinewest]

example of what I have and desired results:

[InvoiceHeader]
InvoiceID,InvoiceNo, ClientID
1, 1004, 20
2, 1007, 33
3, 1009, 55
4, 1011, 22
5, 1012, 23
6, 1014, 20
7, 1015, 33
8, 1016, 33

[InvoiceItemList]
InvoiceID, ProductID, Description, Qty
1, X, Widget, 1
1, Y, Widget, 1
1, Z, Widget, 1
2, X, Widget, 1
3, Y, Widget, 1
4, X, Widget, 1
4, Z, Widget, 1
5, X, Widget, 1
6, X, Widget, 1
6, Z, Widget, 1
7, Y, Widget, 1
8, X, Widget, 1
8, Y, Widget, 1

ideally, the results should be:

X sold with Y = 2
InvoiceID, ClientID, ItemID, Description, Qty
1004, 20, X, Widget, 1
1016, 33, X, Widget, 1

X sold without Y = 4
InvoiceID, ClientID, ItemID, Description, Qty
1007, 33, X, Widget, 1
1011, 22, X, Widget, 1
1012, 23, X, Widget, 1
1014, 20, X, Widget, 1

[Shashi Sadasivan]

example of what I have and desired results:

[InvoiceHeader]
InvoiceID,InvoiceNo, ClientID
1, 1004, 20
2, 1007, 33
3, 1009, 55
4, 1011, 22
5, 1012, 23
6, 1014, 20
7, 1015, 33
8, 1016, 33

[InvoiceItemList]
InvoiceID, ProductID, Description, Qty
1, X, Widget, 1
1, Y, Widget, 1
1, Z, Widget, 1
2, X, Widget, 1
3, Y, Widget, 1
4, X, Widget, 1
4, Z, Widget, 1
5, X, Widget, 1
6, X, Widget, 1
6, Z, Widget, 1
7, Y, Widget, 1
8, X, Widget, 1
8, Y, Widget, 1

ideally, the results should be:

X sold with Y = 2
InvoiceID, ClientID, ItemID, Description, Qty
1004, 20, X, Widget, 1
1016, 33, X, Widget, 1

X sold without Y = 4
InvoiceID, ClientID, ItemID, Description, Qty
1007, 33, X, Widget, 1
1011, 22, X, Widget, 1
1012, 23, X, Widget, 1
1014, 20, X, Widget, 1

For the first query

select count(*) from InvoiveItemlist IL , InvoiceHeader IH where IL.invoiceID = IH.InvoiceID and IL.productID = 'X' and IL.product ID in (Select IL1.ProductID from InvoiceItemList IL1 where IL1.ProductID = IL.ProductID and IL1.ProductID = 'Y')

Does that help?
if yes build upon this for the next one. Let us know if you get through.

cheers