Difficult select statement... - SQL Server Forums

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)

Difficult select statement...

Author

Topic

tentonipete
Starting Member

4 Posts

Posted - 2007-07-03 : 12:57:30

I have the following tables

A
qID Question
1 What day is it?
2 How many are there?
3 Do you have green?

B
qID CategoryID
1 1
1 3
2 1
3 2

The problem I have is that I am trying to apply filters to the questions to return them to the user. It is fine if i want to just return say all of the Questions that are in CategoryID = 1.

I can just do

SELECT A.QuestionID, A.Question, B.CategoryID
FROM A INNER JOIN B ON A.qID = B.qID
WHERE CategoryID = 1

This will return

qID Question CategoryID
1 What day is it? 1
2 How many are there? 1

I can even return which questions are in either CategoryID = 1 OR CategoryID = 3.

qID Question CategoryID
1 What day is it? 1
1 What day is it? 3
2 How many are there? 1

then use the DESTINCT to only return qID= 1 once.

The problem I have is that when I apply two Category Filters, CategoryID = 1 and CategoryID = 3 then I will not work. I am completely aware of why this is, but is there not a way where I can look through the table and return the question only when it appears twice, once with CategoryID = 1 and once with CategoryID = 3?

I hope I made this pretty clear. Thanks in advance for any help.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-03 : 13:19:37

Maybe something like:

SELECT *
FROM A
WHERE EXISTS (
        SELECT *
        FROM B B1
        WHERE A.qID = B1.qID
            AND B1.CategoryID = 1
    )
    AND EXISTS (
        SELECT *
        FROM B B2
        WHERE A.qID = B2.qID
            AND B2.CategoryID = 3
    )

SELECT A.*
FROM A
    JOIN (
        SELECT B.qID
        FROM B
        WHERE B.CategoryID IN (1,3)
        GROUP BY B.qID
        HAVING COUNT(*) = 2
    ) D
    ON A.qID = D.qID

etc

tentonipete
Starting Member

4 Posts

Posted - 2007-07-31 : 13:28:04

Thanks for your help, in the end I went with a variation on the second of your suggestions as that seemed simpler given my data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources