joining 5 tables

Tables structure:

INVOICE Table TELEVISION Table INV_LINE_ITEM Table

InvoiceDt Type InvoiceNbrFK

InvoiceNbrPK Manufacturer SerialNumberFK

SalesPersonIDFK size Quantity

TotalPrice Weight

CustomerIDFK SerialNumberPK LineNbr

TELEVISION_RETURNED SALESPERSON

SerialNumberFK SalesPersonIDPK

RepairDt Name

ReturnAcknowledge Initial

I need to join several tables to find the salesperson who has sold the most Television type and version that were returned for defect. I need to project this result:

SalesPersonID SalesPerson Name Type+Version Times Returned
- - _ _ _ _ _ _

I started working on some tables to find the type and version of television that were the most returned to the store. I came out with this query:

Select top 1

Type+version ' Type/Version',

count(type+version) 'Time return'

From Television T, Televison_Returned TR, inv_line_item inv, invoice i

Where i.invoicenbr = inv.fk_invoicenbr

and inv.fk_serialNumber = T.serialnumber and inv.fk_SerialNumber = TR.fk_SerialNumber

and TR.fk_serialNbr = T.serialnumber and TR.fk_TelevisionNbr in

(select fk_SerialNumber

from inv_line_item

where fk_invoicenbr in

(Select invoicenbr

from invoice

))

group by type, version

order by count(*) desc;

But I cannot connect the employee table with this query to find the employee who sold this type and version of televison that is always returned for the defect to the store.

Help will be appreciated.

Maybe something like this?

Note that it is possible for more than one salesperson to sell a particularly troublesome type of television, so more than one row will be returned.

Chris

DECLARE @.INVOICE TABLE (InvoiceNbrPK INT, CustomerIDFK INT, SalesPersonIDFK 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, SalesPersonIDFK)
SELECT 1, 1, 1 UNION
SELECT 2, 2, 2 UNION
SELECT 3, 3, 3 UNION
SELECT 4, 4, 1

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

DECLARE @.SALESPERSON TABLE (SalesPersonIDPK INT, [Name] VARCHAR(30))
INSERT INTO @.SALESPERSON(SalesPersonIDPK, [Name])
SELECT 1, 'K H Tan' UNION
SELECT 2, 'Konstantin Kosinsky' UNION
SELECT 3, 'lkh'

DECLARE @.TELEVISION_RETURNED TABLE (SerialNumberFK INT)
INSERT INTO @.TELEVISION_RETURNED(SerialNumberFK)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3

SELECT x.[SalesPersonID],
x.[SalesPerson Name],
x.[Type],
x.[Count Returned]
FROM
(
SELECT s.SalesPersonIDPK AS [SalesPersonID],
s.[Name] AS [SalesPerson Name],
t.[Type],
cr.[Count Returned]
FROM @.INVOICE i
INNER JOIN @.INV_LINE_ITEM ilt ON ilt.InvoiceNbrFK = i.InvoiceNbrPK
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = ilt.SerialNumberFK
INNER JOIN @.SALESPERSON s ON s.SalesPersonIDPK = SalesPersonIDFK
INNER JOIN (SELECT t.[Type],
COUNT(*) AS [Count Returned]
FROM @.TELEVISION_RETURNED tr
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = tr.SerialNumberFK
GROUP BY [Type]) cr ON cr.[Type] = t.[Type]
GROUP BY s.SalesPersonIDPK, s.[Name], t.[Type], cr.[Count Returned]
) x

INNER JOIN ( SELECT TOP 1
cr.[Type],
cr.[Count Returned] AS [Max Count Returned]
FROM
(SELECT t.[Type],
COUNT(*) AS [Count Returned]
FROM @.TELEVISION_RETURNED tr INNER JOIN @.TELEVISION t ON t.SerialNumberPK = tr.SerialNumberFK
GROUP BY t.[Type]) cr
ORDER BY cr.[Count Returned] DESC) y ON y.[Type] = x.[Type] AND y.[Max Count Returned] = x.[Count Returned]

|||

Hi Chris,

I am going to work on your example and will see what I get as projection. Or either, can there be a simple query to return the salesperson who have sold out more of the defectious TV?

|||

I've just taken another look at the previous example I posted, and it can be simplified slightly. See below.

Chris

DECLARE @.INVOICE TABLE (InvoiceNbrPK INT, CustomerIDFK INT, SalesPersonIDFK 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, SalesPersonIDFK)
SELECT 1, 1, 1 UNION
SELECT 2, 2, 2 UNION
SELECT 3, 3, 3 UNION
SELECT 4, 4, 1

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

DECLARE @.SALESPERSON TABLE (SalesPersonIDPK INT, [Name] VARCHAR(30))
INSERT INTO @.SALESPERSON(SalesPersonIDPK, [Name])
SELECT 1, 'K H Tan' UNION
SELECT 2, 'Konstantin Kosinsky' UNION
SELECT 3, 'lkh'

DECLARE @.TELEVISION_RETURNED TABLE (SerialNumberFK INT)
INSERT INTO @.TELEVISION_RETURNED(SerialNumberFK)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3

SELECT s.SalesPersonIDPK AS [SalesPersonID],
s.[Name] AS [SalesPerson Name],
t.[Type],
y.[Max Count Returned]
FROM @.INVOICE i
INNER JOIN @.INV_LINE_ITEM ilt ON ilt.InvoiceNbrFK = i.InvoiceNbrPK
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = ilt.SerialNumberFK
INNER JOIN @.SALESPERSON s ON s.SalesPersonIDPK = SalesPersonIDFK
INNER JOIN ( SELECT TOP 1
cr.[Type],
cr.[Count Returned] AS [Max Count Returned]
FROM
(SELECT t.[Type],
COUNT(*) AS [Count Returned]
FROM @.TELEVISION_RETURNED tr
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = tr.SerialNumberFK
GROUP BY t.[Type]) cr
ORDER BY cr.[Count Returned] DESC) y ON y.[Type] = t.[Type]
GROUP BY s.SalesPersonIDPK, s.[Name], t.[Type], y.[Max Count Returned]