at least one item among all

I do not know how to project the customers who bought at least one of each of the color tv, white/black, mono. It is like finding which customer bought 1 tv from each type available at the same time. in the TELEVISION table the types are: color, white/black, mono.

As an example someone buys 5 tv among them, he has 2 color tv, 1 white/black, 2 mono however he did purchase one of each type of tv.

I need to project this output:

CustID Customer Name State

-- --

101 Jim White Ohio

234 Karl Smith Pennsylvania

Here is the tables structure

INVOICE Table TELEVISION Table INV_LINE_ITEM Table

InvoiceDt Type InvoiceNbrFK

InvoiceNbrPK Manufacturer SerialNumberFK

EmpIDFK size Quantity

TotalPrice Weight

CustomerIDFK SerialNumberPK LineNbr

CUSTOMER Table

CustomerIDPK

FName

LName

Address

City

State

Hi Mermaid

The example below will return the data you requested.

Chris

DECLARE @.INVOICE TABLE (InvoiceNbrPK INT, CustomerIDFK INT)
DECLARE @.TELEVISION TABLE (Type VARCHAR(13), SerialNumberPK INT)
DECLARE @.INV_LINE_ITEM TABLE (InvoiceNbrFK INT, SerialNumberFK INT, Quantity INT)
DECLARE @.CUSTOMER TABLE (CustomerIDPK INT, FName VARCHAR(20), LName VARCHAR(20))

INSERT INTO @.TELEVISION(Type, SerialNumberPK)
SELECT 'Mono', 1 UNION
SELECT 'Colour', 2 UNION
SELECT 'Black & White', 3

INSERT INTO @.INVOICE(InvoiceNbrPK, CustomerIDFK)
SELECT 1, 1 UNION
SELECT 2, 2 UNION
SELECT 3, 3 UNION
SELECT 4, 4

INSERT INTO @.CUSTOMER(CustomerIDPK, FName, LName)
SELECT 1, 'Kent', 'Waldrop' UNION
SELECT 2, 'Arnie', 'Rowland' UNION
SELECT 3, 'Louis', 'Davidson' UNION
SELECT 4, 'Umachandar', 'Jayachandran'

--Set up the data so that Kent and Umachander have both bought one of each TV
INSERT INTO @.INV_LINE_ITEM(InvoiceNbrFK, SerialNumberFK, Quantity)
SELECT 1, 1, 3 UNION
SELECT 1, 2, 2 UNION
SELECT 1, 3, 1 UNION
SELECT 2, 2, 5 UNION
SELECT 2, 3, 2 UNION
SELECT 3, 2, 3 UNION
SELECT 4, 1, 1 UNION
SELECT 4, 2, 1 UNION
SELECT 4, 3, 1

SELECT c.CustomerIDPK AS [CustID],
c.FName + ' ' + c.LName AS [Customer Name]
FROM @.CUSTOMER c
INNER JOIN @.INVOICE i ON i.CustomerIDFK = c.CustomerIDPK
INNER JOIN @.INV_LINE_ITEM ilt ON ilt.InvoiceNbrFK = i.InvoiceNbrPK
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = ilt.SerialNumberFK
WHERE ilt.Quantity > 0
GROUP BY c.CustomerIDPK,
c.FName + ' ' + c.LName
HAVING COUNT(DISTINCT t.Type) = (SELECT COUNT(DISTINCT t2.Type) FROM @.Television t2)

|||

Maybe something like this?

declare @.customer table
( custId integer,
FName varchar(20),
LName varchar(20),
State varchar(30)
)
insert into @.customer values (101, 'Jim', 'White', 'Ohio')
insert into @.customer values (102, 'Donna', 'White', 'Illinois')
insert into @.customer values (234, 'Karl', 'Smith', 'Pennsylvania')
insert into @.customer values (235, 'Karl', 'Benson', 'Guam')
--select * from @.customer

declare @.invoice table
( invoiceNbr varchar(15),
custId integer
)
insert into @.invoice values (1, 101)
insert into @.invoice values (2, 102)
insert into @.invoice values (3, 234)
insert into @.invoice values (4, 234)
insert into @.invoice values (5, 234)
--select * from @.invoice

declare @.television table
( serialNumber varchar(25),
type varchar(12)
)
insert into @.television values ('Dlx-0001', 'Color')
insert into @.television values ('Dlx-0002', 'Color')
insert into @.television values ('Dlx-0003', 'Color')
insert into @.television values ('Clr-0001', 'Color')

insert into @.television values ('BW-00001', 'white/black')
insert into @.television values ('BW-00002', 'white/black')

insert into @.television values ('Mon-0001', 'Mono')
insert into @.television values ('Mon-0002', 'Mono')
insert into @.television values ('Mon-0003', 'Mono')
--select * from @.television

declare @.inv_line_item table
( invoiceNbr varchar(15),
serialNumber varchar(25)
)
insert into @.inv_line_item values (1, 'Dlx-0001')
insert into @.inv_line_item values (1, 'BW-00001')
insert into @.inv_line_item values (1, 'Mon-0001')

insert into @.inv_line_item values (2, 'Dlx-0003')
insert into @.inv_line_item values (2, 'Mon-0003')

insert into @.inv_line_item values (3, 'Dlx-0002')
insert into @.inv_line_item values (4, 'BW-00002')
insert into @.inv_line_item values (5, 'Mon-0002')
--select * from @.inv_line_item

select custId,
[Customer Name],
state
from ( select c.custId,
c.fName + ' ' + c.LName as [Customer Name],
state,
count(distinct t.type) as typeCount
from @.customer c
inner join @.invoice i
on c.custId = i.custId
inner join @.inv_line_item l
on i.invoiceNbr = l.invoiceNbr
inner join @.television t
on l.serialNumber = t.serialNumber
and t.type in ('Color', 'Mono', 'White/Black')
group by c.custId,
c.state,
c.lName,
c.fName
having count(distinct t.type) = 3
) x

-- custId Customer Name state
--
-- 101 Jim White Ohio
-- 234 Karl Smith Pennsylvania

I like Chirs' solution better; I thought about doing it like that but I didn't have the HAVING condition worked out like that. Nice!

|||

Hi Chris,

I will try your solution and see what will be the output.

Thank you very much.

|||

Hi Chris,

Your example did return what I was expecting as projection. Thank you so much for the hint, you made my day.

Thanks again.

|||

Hi Kent,

I prefer Chris solution because you set up the count to be equal to 3. I tried your example and got something else because of the count. I really appreciate your input and thank you for helping me out. I can breath now, .

|||

Hi Mermaid
That's interesting. Do you have more than three distinct types in the Television table? You did specify only three in your initial post. Other than the hard-coding, both Kent's and my solutions are virtually identical.
I must admit that when I put my version together I was in two minds over whether or not to hard-code the number of distinct types into the HAVING clause.
Chris

|||

Here is another simpler and slightly better performing way to answer the question. I used the schema that Chris posted. The gist of the solution is that you get customers that do not have any television sets that are not accounted for in their invoices. This basically checks the inverse condition. This query will perform orders of magnitude faster than the GROUP BY approach due to lesser scans, no aggregation and simpler joins.

SELECT c.CustomerIDPK AS [CustID],
c.FName + ' ' + c.LName AS [Customer Name]
FROM @.CUSTOMER c
WHERE NOT EXISTS(

SELECT *

FROM @.TELEVISION AS t
WHERE NOT EXISTS(
SELECT *
FROM @.INVOICE AS i
JOIN @.INV_LINE_ITEM AS ilt
ON i.CustomerIDFK = c.CustomerIDPK
WHERE ilt.InvoiceNbrFK = i.InvoiceNbrPK
and t.SerialNumberPK = ilt.SerialNumberFK
and ilt.Quantity > 0))

And you can actually simplify the GROUP BY query by doing below.

SELECT c.CustomerIDPK AS [CustID],
c.FName + ' ' + c.LName AS [Customer Name]
FROM @.CUSTOMER c
WHERE EXISTS(
SELECT 1
FROM @.INVOICE i
INNER JOIN @.INV_LINE_ITEM ilt ON ilt.InvoiceNbrFK = i.InvoiceNbrPK
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = ilt.SerialNumberFK
WHERE ilt.Quantity > 0
And i.CustomerIDFK = c.CustomerIDPK
HAVING COUNT(DISTINCT t.Type) = (SELECT COUNT(DISTINCT t2.Type) FROM @.Television t2))

|||

Hi,

I try your first example the projection did not return anything. I think that the reverse is not working but your second example works the same way as Chris's. I noted both queries and thank you for your input. I do appreciate your help.

|||Could you please post the data / schema for which the first query doesn't work? That logic looks fine to me.|||

HI umachandar,

I worked on your example last night and the result is correct. Thanks for helping me out.