Query to find customers that ordered x AND y AND z
-
I need a query for the following (simplified) situation:
There is a table with customers, a table with products, and a link table that links many customers to many products.
The query I need will find all customers that ordered product x AND product y AND product z (etc ...)
Can anyone help me the right SELECT ... for this problem
-
IF EXISTS (Select * from dbo.SysObjects where name = 'fnSplit_Set' and XType IN ('FN', 'IF', 'TF'))
DROP FUNCTION dbo.fnSplit_Set
GO
IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U')
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
DECLARE @Ids as varchar(8000)
SET @IDS = ''
Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds
SET @IDS = left(@IDS, ABS(len(@IDS) - 1))
PRINT @IDS
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank
--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.
GO
IF NOT Object_id('SearchColumns') IS NULL
DROP PROCEDURE SearchColumns
GO
CREATE PROCEDURE dbo.SearchColumns @Items as varchar(8000), @ItemsCount as smallint --avoid calling split twice
AS
SET NOCOUNT ON
SELECT
O.id
, O.Name
FROMdbo.SysObjects O
WHERE Exists(
SELECT 1
FROMdbo.SysColumns C
WHERE C.id = O.id and C.Name in (Select EachID from dbo.fnSplit_Set(@Items, ','))
GROUP BY id having count(*) >= @ItemsCount
)
ORDER BYName
SET NOCOUNT OFF
GO
--find all items
Exec dbo.SearchColumns 'name,id', 2-- 4 row(s) affected
Exec dbo.SearchColumns 'name,id,indid', 3-- 1 row(s) affected
Exec dbo.SearchColumns 'id', 1-- 12 row(s) affected
--find at least 2
Exec dbo.SearchColumns 'name,id,indid', 2-- 5 row(s) affected (instead of 1)
--find at least 1
Exec dbo.SearchColumns 'name', 1-- 10 row(s) affected
Exec dbo.SearchColumns 'name,indid', 1-- 11 row(s) affected
GO
DROP PROCEDURE SearchColumns
--DROP FUNCTION fnSplit_Set
GO
-
Below is a fairly simple method. Note joining the table on itself (link table) can be cpu intensive.
SELECT distinct ct.customer_name
FROM (select customerid from linktable where productlid = a) a
JOIN (select customerid from linktable where productlid = b) b
on a.customerid = b.customerid
JOIN (select customerid from linktable where productlid = b) c
on b.customerid = c.customerid
JOIN customer_table ct ON ct.customerid = a.customerid
-
Yes but this doesn't allow for a dynamic number of parameters.
-
Sorry, neglected to catch that in the question.
Another option would be to create the sql statement in a cursor
and then exectue the statement. Storing the result in a tmp table.
cursor
...
WHILE @@FETCH_STATUS = 0
begin
strSQL = strSQL + 'JOIN (select customer from linktable where product = 'x') tableident#
ON tableident1.customerid = tableident#.customerid) '
fetch next into
end
insert into #tmpResult
EXEC sp_executeSQL @strSQL
-
Test that on a 1000 rows table with 25 left joins... Now test mine after that...
-
Very correct, the above derived tables on the original link_table performance would rapidly degrade.
The simplest approach
SELECT distinct customer
FROM customer join link-table
where link-table.product in (yada)
-
Insert list of queried produxct to temp table #QProduct
SELECT CustomerId
FROM Customer C
Inner join CustomerProduct CP on CustomerId = CP.CustomerId
Inner join Product P on P.Productd = CP.ProductId
Inner join #QproductId QP on QP.ProductCode (??) = P.ProductCode
Group by CustomerId
HAVING count(QP.ProductCode) = (select count(ProductCode) from QProduct)
_____________
Code for TallyGenerator -
And if you read the poster correctly you notice that the many-to-many is already populated therefore simplifiying Sergiy's strategy to:
SELECT l.CustomerId
FROM LINKEDTABLE l join #QproductId QP on QP.Productid = l.ProductId
Group by l.CustomerId
HAVING count(l.Productid) = (select count(*) from #QProductid)
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply