selecting based on multiple rows

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 2008 Forums

Transact-SQL (2008)

selecting based on multiple rows

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2012-02-16 : 11:57:13

i'm having trouble coming up with a way of selecting from a database as follows. I've included examples of my tables (which unfortunately i cannot change or create new ones) and some example data. any help or pointers are extremely appreciated as ever!

table 1 - tblDownloads

downloadID, filename

1, file1.pdf
2, file2.pdf

table 2 - tblAllocation

allocationID, downloadID, categoryID

1, 1, 1
1, 1, 2
1, 2, 2

select * from tblDownloads where (and here is where i drop into pseudo code!)

we can find a row in tblAllocation that matches downloadID (e.g. 1) and has both an entry matching categoryID 1 & 2

so basically there must be an entry in tblAllocation for 2 specified categories. so if i used the data above i would only return file1.pdf

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-16 : 12:47:44

[code]SELECT
*
FROM
tblDownloads d
WHERE
EXISTS
(
SELECT
COUNT(DISTINCT categoryId)
FROM
tblAllocation a
WHERE
a.downloadID = d.downloadID
AND a.CategoryId IN (1, 2)
GROUP BY
a.downloadID
HAVING
COUNT(DISTINCT categoryId) = 2
)[/code]

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2012-02-17 : 04:23:38

So i select and when my count equals 2 then i know i've found my entry. makes sense now. as ever my thanks to the collective knowledge of this site!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources