October 29, 2009 at 7:00 am
Hello All
I have a query as with following situation
Here are the sample db structure
1. tbl_Contacts -
ContactID, ContactName, Address..etc
2. tbl_Products -
ProductID, ContactID
There is one-to-many relationship between these two tables i.e. one Contact has purchased many Products.
The query is... I want to fetch a list of all the Contacts having purchased a list of ALL the Products given in a variable i.e. All the Contacts who purchased ALL the Products given in a variable list @var1=12,14,17,30.
When I used "IN" clause then the query returns the list of all the contacts who purchased any of the products in the given variable list... which is incorrect.
Also, I dont want to use INTERSECT, UNION etc...
Please help...
October 29, 2009 at 7:27 am
-- Stupid suggestion - ignore
October 29, 2009 at 7:35 am
DECLARE @tbl_Contacts TABLE (
ContactID int,
ContactName varchar(50)
)
DECLARE @tbl_Products TABLE (
ContactID int,
ProductID int
)
INSERT INTO @tbl_Contacts VALUES (1,'John')
INSERT INTO @tbl_Contacts VALUES (2,'Mike')
INSERT INTO @tbl_Contacts VALUES (3,'Helen')
INSERT INTO @tbl_Contacts VALUES (4,'Andy')
-- Products for John
INSERT INTO @tbl_Products VALUES (1,1)
INSERT INTO @tbl_Products VALUES (1,2)
INSERT INTO @tbl_Products VALUES (1,3)
INSERT INTO @tbl_Products VALUES (1,4)
INSERT INTO @tbl_Products VALUES (1,5)
-- Products for Mike
INSERT INTO @tbl_Products VALUES (2,1)
INSERT INTO @tbl_Products VALUES (2,2)
INSERT INTO @tbl_Products VALUES (2,3)
INSERT INTO @tbl_Products VALUES (2,4)
-- Products for Helen
INSERT INTO @tbl_Products VALUES (3,1)
INSERT INTO @tbl_Products VALUES (3,2)
INSERT INTO @tbl_Products VALUES (3,3)
-- Products for Andy
INSERT INTO @tbl_Products VALUES (4,2)
INSERT INTO @tbl_Products VALUES (4,4)
INSERT INTO @tbl_Products VALUES (4,5)
;WITH Products (ContactID, ProductId)
AS (
SELECT ContactID, ProductId
FROM @tbl_Products AS P
WHERE ProductID IN (1,2,3,4)
)
SELECT *
FROM @tbl_Contacts AS C
WHERE EXISTS (
SELECT ContactId
FROM Products AS P
WHERE C.ContactID = P.ContactID
GROUP BY ContactID
HAVING COUNT(DISTINCT ProductId) = (SELECT COUNT(DISTINCT ProductId) FROM Products)
)
-- Gianluca Sartori
November 17, 2010 at 8:53 pm
SELECT DISTINCT CONTACTID,ContactName FROM tbl_Contacts WHERE ContactID IN
(SELECT ContactID FROM tbl_Products GROUP BY ContactID HAVING COUNT(DISTINCT PRODUCTID) = (SELECT COUNT(DISTINCT PRODUCTID) FROM tbl_Products))
Regards,
PraJey
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply