InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 22:02:40 GMT20http://www.sqlservercentral.com/Forums/Topic436307-338-1.aspxHappy new year!Wed, 26 Dec 2007 03:14:21 GMTjacob sebastianhttp://www.sqlservercentral.com/Forums/Topic436307-338-1.aspxI don't know How to Thank You. That Did the Trick. Thanks a lot and I wish you a happy new YearWed, 26 Dec 2007 03:11:24 GMTvblllovehttp://www.sqlservercentral.com/Forums/Topic436307-338-1.aspxSomething like this?;WITH family1 AS ( SELECT DISTINCT InvoiceID FROM invoiceDetails WHERE ItemCode LIKE 'PMM1%'), family2 AS ( SELECT DISTINCT InvoiceID FROM invoiceDetails WHERE ItemCode LIKE 'PMM2%'), family3 AS ( SELECT DISTINCT InvoiceID FROM invoiceDetails WHERE ItemCode LIKE 'PMM3%')SELECT SalesRepID, COUNT(*) FROM Invoices iINNER JOIN family1 f1 ON f1.invoiceID = i.InvoiceIDINNER JOIN family2 f2 ON f2.InvoiceID = i.InvoiceIDINNER JOIN family3 f3 ON f3.InvoiceID = i.InvoiceIDGROUP BY SalesRepIDWed, 26 Dec 2007 02:46:11 GMTjacob sebastianhttp://www.sqlservercentral.com/Forums/Topic436307-338-1.aspxHi,I m having problem in creating a query for counting Articles of a same FAmily in an Invoice: I have two Tables Invoice, InvoiceDetails that are related through InvoiceId.In Invoice Table I keep the Salesman Code. In InvoiceDetails I keep the Articles that they Sell ( Marlboro Family). The Marlboro Family is defines as three GRoups (Marlboro Light, Marlboro Ultra Light and Marlboro Full Flavour) . Their Code is like:PMM1112, PMM1212, PMM1313 (MArlboro Light)PMM2111, PMM2112, PMM2112 (Marlboro Full Flavour)PMM3111 (Marlboro Ultra Light)Now I have to count the Invoices for Each Salesman that have an Invoice that Include all Three of them in one InvoiceE.c InvoiceId 00001 (PMM1112, PMM1212, PMM2111, PMM3111).Thanks in AdvanceWed, 26 Dec 2007 02:31:38 GMTvblllove