Select multiple rows based on list of values? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select multiple rows based on list of values?

Author

Topic

Amberite
Starting Member

11 Posts

Posted - 2007-08-28 : 13:50:43

Hello,

I have a slightly complex problem. I have a table called Product, and another called ProductGenre. In the ProductGenre table, each productID is mapped to a genreID. When JOINing the two tables, you get something like this:


productID   genreID
=========   =======
13          1
13          4
13          7
28          4
28          9
...

Now here's the rub: I have a stored procedure which is passed a string of desired genreIDs (ie, '1,4'). In the query, I use a Split function to make the string into a table, and then use 'WHERE genreID IN dbo.Split(@genreIDs)', etc.

However, the problem is that, based on the example of a string of '1,4' passed to the function, only productID #13 should be returned (because it has BOTH 1 and 4 genreIDs). The query, however, will return products 13 and 28 because the IN function will look for any occurance of 1 OR 4 in genreIDs. I need something to look for 1 AND 4.

Anyone have any idea how to do this without completely throwing efficiency and speed out the window?

Thanks! :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-28 : 13:58:13

[code]DECLARE @TABLE TABLE
(
productID int,
genreID int
)

INSERT INTO @TABLE
SELECT 13, 1 UNION ALL
SELECT 13, 4 UNION ALL
SELECT 13, 7 UNION ALL
SELECT 28, 4 UNION ALL
SELECT 28, 9

DECLARE @csv varchar(100)

SELECT @csv = '1,4'

SELECT t.productID
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
FROM @TABLE t INNER JOIN CSVTable(@csv) c
ON t.genreID = c.numberval
GROUP BY t.productID
HAVING COUNT(*) = (SELECT COUNT(*) FROM CSVTable(@csv))[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 14:03:47

[code]DECLARE @ProductGenre TABLE (ProductID INT, GenreID INT)

INSERT @ProductGenre
SELECT 13, 1 UNION ALL
SELECT 13, 4 UNION ALL
SELECT 13, 7 UNION ALL
SELECT 28, 4 UNION ALL
SELECT 28, 9

DECLARE @GenreIDs VARCHAR(8000)

SET @GenreIDs = '1,4'

;WITH Yak (Data)
AS (
SELECT Data
FROM dbo.fnParseList(',', @GenreIDs)
), Yak2 (ProductID, GenreID)
AS (
SELECT pg.ProductID,
pg.GenreID
FROM @ProductGenre AS pg
INNER JOIN Yak AS x ON x.Data = pg.GenreID
), Yak3 (ProductID)
AS (
SELECT ProductID
FROM Yak2
GROUP BY ProductID
HAVING COUNT(DISTINCT GenreID) = (SELECT COUNT(DISTINCT Data) FROM Yak)
)

SELECT y2.ProductID,
y2.GenreID
FROM Yak2 AS y2
INNER JOIN Yak3 AS y3 ON y3.ProductID = y2.ProductID[/code]

E 12°55'05.25"
N 56°04'39.16"

Amberite
Starting Member

11 Posts

Posted - 2007-08-28 : 14:17:35

Khtan, that's great! The HAVING clause is what I guess I'm missing.

However, I oversimplified the original query a little bit. In the real one, I actually have 5-6 CSVs which are queried, so I'm a little confused as to how to make the HAVING clause work in this case.

Here is the actual query:


SELECT pg2.GenreID AS id, count(distinct pg2.ProductID) AS [count]
FROM
	ProductGenre pg1
	left join ProductGenre pg2	    with (nolock) on pg1.productID = pg2.productID
	left join Product p		    with (nolock) on pg1.ProductID = p.ProductID
	left join ProductVariant pva        with (nolock) on pg1.ProductID = pva.ProductID
	left join productcategory pc	    with (nolock) on pg1.ProductID = pc.ProductID 
	left join productsection ps	    with (nolock) on pg1.ProductID = ps.ProductID 
	left join ProductManufacturer pm    with (nolock) on pg1.ProductID = pm.ProductID 
	left join ProductDistributor pd	    with (nolock) on pg1.ProductID = pd.ProductID 
	left join ProductVector pve	    with (nolock) on pg1.ProductID = pve.ProductID 
WHERE 
	  (pc.categoryid IN (SELECT convert(int, Items) FROM dbo.Split(@categoryID, ',')))
          and (ps.sectionid IN (SELECT convert(int, Items) FROM dbo.Split(@sectionID, ',')))
          and (pm.manufacturerid IN (SELECT convert(int, Items) FROM dbo.Split(@manufacturerID, ',')))
          and (pd.DistributorID IN (SELECT convert(int, Items) FROM dbo.Split(@distributorID, ',')))
          and (pg1.GenreID IN (SELECT convert(int, Items) FROM dbo.Split(@genreID, ',')))
          and (pve.VectorID IN (SELECT convert(int, Items) FROM dbo.Split(@vectorID, ',')))
GROUP BY pg2.GenreID

Could using your code work in this situation as well?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 14:42:15

[code]SELECT pg.GenreID AS ID,
COUNT(DISTINCT pg.ProductID) AS [Count]
FROM ProductGenre AS pg WITH (NOLOCK)
CROSS JOIN dbo.Split(@CategoryID, ',') AS c
CROSS JOIN dbo.Split(@SectionID, ',') AS s
CROSS JOIN dbo.Split(@ManufacturerID, ',') AS m
CROSS JOIN dbo.Split(@DistributorID, ',') AS d
CROSS JOIN dbo.Split(@GenreID, ',') AS g
CROSS JOIN dbo.Split(@VectorID, ',') AS v
WHERE pg.CategoryID = c.Items
AND pg.SectionID = s.Items
AND pg.ManufacturerID = m.Items
AND pg.DisitributorID = d.Items
AND pg.GenreID = g.Items
AND pg.VectorID = v.Items
GROUP BY pg.GenreID
ORDER BY pg.GenreID

SELECT pg.GenreID AS ID,
COUNT(DISTINCT pg.ProductID) AS [count]
FROM ProductGenre AS pg WITH (NOLOCK)
WHERE EXISTS (SELECT * FROM dbo.Split(@CategoryID, ',') AS c WHERE c.Items = pg.CategoryID)
AND EXISTS (SELECT * FROM dbo.Split(@SectionID, ',') AS s WHERE s.Items = pg.SectionID)
AND EXISTS (SELECT * FROM dbo.Split(@ManufacturerID, ',') AS m WHERE m.Items = pg.ManufacturerID)
AND EXISTS (SELECT * FROM dbo.Split(@DistributorID, ',') AS d WHERE d.Items = pg.DistributorID)
AND EXISTS (SELECT * FROM dbo.Split(@GenreID, ',') AS g WHERE g.Items = pg.GenreID)
AND EXISTS (SELECT * FROM dbo.Split(@VectorID, ',') AS v WHERE v.Items = pg.VectorID)
GROUP BY pg.GenreID[/code]

E 12°55'05.25"
N 56°04'39.16"

Amberite
Starting Member

11 Posts

Posted - 2007-08-28 : 15:27:54

Khtan, unfortunately your method of using the count of CSV items doesn't work with the amount of tables I have :(

Peso, thank you so much for taking the time to help me. I've looked through your code, and you definitely know a lot more than I do! :)

I tested your code, and it works great! However, one small problem: if any of the variables are null (ie @CategoryID, @SectionID, etc), then the procedure won't return anything. What's the easiest way to get around this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 15:32:28

[code]WHERE (@CategoryID IS NULL OR EXISTS (SELECT * FROM dbo.Split(@CategoryID, ',') AS c WHERE c.Items = pg.CategoryID))
AND (@SectionID IS NULL OR EXISTS (SELECT * FROM dbo.Split(@SectionID, ',') AS s WHERE s.Items = pg.SectionID))
AND (@ManufacturerID IS NULL OR EXISTS (SELECT * FROM dbo.Split(@ManufacturerID, ',') AS m WHERE m.Items = pg.ManufacturerID))
AND (@DistributorID IS NULL OR EXISTS (SELECT * FROM dbo.Split(@DistributorID, ',') AS d WHERE d.Items = pg.DistributorID))
AND (@GenreID IS NULL OR EXISTS (SELECT * FROM dbo.Split(@GenreID, ',') AS g WHERE g.Items = pg.GenreID))
AND (@VectorID IS NULL OR EXISTS (SELECT * FROM dbo.Split(@VectorID, ',') AS v WHERE v.Items = pg.VectorID))
GROUP BY pg.GenreID[/code]

E 12°55'05.25"
N 56°04'39.16"

Amberite
Starting Member

11 Posts

Posted - 2007-08-28 : 15:39:49

Peso I absolutely love you :)

The second SELECT is working perfectly. The first one doesn't produce anything. Actually, what is the difference between these two??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 15:52:05

I don't see why my last query shouldn't work.
Are you sure the @GenreID really is NULL and not an empty string?

E 12°55'05.25"
N 56°04'39.16"

Amberite
Starting Member

11 Posts

Posted - 2007-08-28 : 15:56:30

quote:
Originally posted by Peso

I don't see why my last query shouldn't work.
Are you sure the @GenreID really is NULL and not an empty string?

Peso, sorry I should have been clearer. The second query works perfectly. It's this query that doesn't work if any of the values are NULL:


SELECT		pg.GenreID AS ID,
		COUNT(DISTINCT pg.ProductID) AS [Count]
FROM		ProductGenre AS pg WITH (NOLOCK)
CROSS JOIN	dbo.Split(@CategoryID, ',') AS c
CROSS JOIN	dbo.Split(@SectionID, ',') AS s
CROSS JOIN	dbo.Split(@ManufacturerID, ',') AS m
CROSS JOIN	dbo.Split(@DistributorID, ',') AS d
CROSS JOIN	dbo.Split(@GenreID, ',') AS g
CROSS JOIN	dbo.Split(@VectorID, ',') AS v
WHERE		pg.CategoryID = c.Items
		AND pg.SectionID = s.Items
		AND pg.ManufacturerID = m.Items
		AND pg.DisitributorID = d.Items
		AND pg.GenreID = g.Items
		AND pg.VectorID = v.Items
GROUP BY	pg.GenreID
ORDER BY	pg.GenreID

Amberite
Starting Member

11 Posts

Posted - 2007-08-28 : 18:29:28

I got it working, with a combination of Peso's and Khtan's methods. Thanks to both of you! :)

Peso, I still couldn't get that one SELECT statement to work with NULL values, but I guess its a moot point now anyways.

Thanks again!

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 07:28:38

Check your SPLIT function is high performance (won't matter much if your @Parameter only has one tor two values, matters a LOT if it has lots of values)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Best%20split%20functions

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-29 : 07:39:49

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2007-08-31 : 02:58:26

DECLARE @TABLE TABLE
(
productID int,
genreID int
)
INSERT INTO @TABLE
SELECT 13, 1 UNION ALL
SELECT 13, 4 UNION ALL
SELECT 13, 7 UNION ALL
SELECT 28, 4 UNION ALL
SELECT 28, 9 union all
select 28, 7

DECLARE @T TABLE
(
productID int,
genreID int
)

declare @csv varchar(100)
Set @csv = '1,4'

Insert into @T
Select ProductId,genreID from @Table
where ','+@csv+',' like '%,'+ Cast (GenreId as Varchar(100)) + ',%'

Select distinct t.ProductId
from @Table A
Inner join @T T on T.ProductId = a.ProductId
Group By T.ProductId,A.genreID HAVING count(*) >= 2

ranganath
Posting Yak Master

209 Posts

Posted - 2007-09-01 : 01:20:03

DECLARE @TABLE TABLE
(
productID int,
genreID int
)
INSERT INTO @TABLE
SELECT 13, 1 UNION ALL
SELECT 13, 4 UNION ALL
SELECT 13, 7 UNION ALL
SELECT 28, 4 UNION ALL
SELECT 28, 9 union all
select 28, 7 union all
Select 28,1

DECLARE @T TABLE
(
productID int,
genreID int
)

declare @csv varchar(100)
Set @csv = '1,4,7,9'

Insert into @T
Select ProductId,genreID from @Table
where ','+@csv+',' like '%,'+ Cast (GenreId as Varchar(100)) + ',%'

Select distinct t.ProductId
from @Table A
Inner join @T T on T.ProductId = a.ProductId
Group By T.ProductId,A.genreID HAVING count(*) >= (select count(distinct genreID) From @T)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources