IN operation that matches ALL instead of ANY? - 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)

IN operation that matches ALL instead of ANY?

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2008-03-25 : 13:54:16

A standard IN operation is like doing a series of OR statements in your WHERE clause. Is there anything like an IN statement that is like using a series of AND statements instead?

I tried looking into the ALL operator but that didn't seem to do it or else I just couldn't figure out how to use it correctly.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-25 : 14:04:20

nope.

maybe not in?

just kidding

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-25 : 14:21:17

Can you explain what you are trying to achomplish because that does not make sense to me. How can any value (5 for example) be equal to mutiple values like (1, 2, 3, 5, 7)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 14:23:30

Please can you specify your complete requirement with some sample data?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-25 : 14:25:17

Just a guess, but are you trying to do something like in this thread?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98127

malachi151
Posting Yak Master

152 Posts

Posted - 2008-03-25 : 15:23:18

In my case I'm looking for keys in a joining table.

Specifically, I have Documents where each Document can have many Authors.

So, I have a Documents table, an Authors table, and a linking table for the many-to-many relationship.

So, if I were to write my query without using IN but replicating its function I would do this:

SELECT d.*
FROM Documents d
INNER JOIN Doc_Auth_Link l ON d.DKey = l.DKey
WHERE
l.AKey = 1 OR l.AKey = 2 OR l.AKey = 3

This is the same as:

SELECT d.*
FROM Documents d
WHERE d.DKey IN (SELECT l.DKey FROM Doc_Auth_Link WHERE l.AKey IN (1,2,3))

These queries are going to give me any documents where any one of those authors is related, but I want a query for finding a document where every author that is specifed is related. I tried working through with AND operators and indeed it is a bit more complicated than I was first thinking.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 15:40:19

SELECT d.*
FROM Documents d
WHERE d.DKey IN
(SELECT d1.DKey
FROM Documents d1
INNER JOIN Doc_Auth_Link l ON d1.DKey = l.DKey
LEFT OUTER JOIN Authors a ON a.AKey =i.AKey
GROUP BY d1.DKey
HAVING SUM(CASE WHEN a.AKey IS NULL THEN 1 ELSE 0 END) =0
)

malachi151
Posting Yak Master

152 Posts

Posted - 2008-03-25 : 15:44:00

I just looked at the example from the link that Lamprey provided. That does seem to be it, but I really don't want to take any of those routes, because this is for a dynamically created query that's already extremely compicated, and adding the selective GROUP BY or other methods would be very, very tricky.

Here is what a realtively simply version of some of the generated queries look like, and this isn't joining to some of the other tables yet. The rules for building it are pretty complicated and this would greatly complicate things. In the places where I am using the IN statements is where I would want to use something else that would be more like an AND operator, but I may just have to let that go if its that tricky.

SELECT d.DOC_Key, DOC_Name, '~/files/'+REPLACE(LOWER(DT_Type),' ','')+'/'+DOC_File As DOC_Link, CAST(DOC_Text AS varchar(500)) As DOC_Text, DT_Type, (SELECT CAST(Count(*) As bit) FROM link_PinnedDocs p WHERE p.DOC_Key = d.DOC_Key AND USER_Key = 1) As Pinned, p.DPT_PublicationDate, i.DIDF_SubmissionDate, n.DNP_PublicationDate , ct.RANK AS CTRANK
FROM data_Documents d INNER JOIN ref_DocType ON DOC_Type = DT_Key
LEFT JOIN doc_Patent p ON d.DOC_Key = p.DOC_Key
LEFT JOIN doc_IDF i ON d.DOC_Key = i.DOC_Key
LEFT JOIN doc_NonPatent n ON d.DOC_Key = n.DOC_Key
INNER JOIN CONTAINSTABLE (data_Documents, DOC_Text, 'lens ~ zoom') ct ON d.DOC_Key = ct.[KEY]
WHERE (DOC_Type = 1) AND (((DPT_IsCDM = 1) AND (DPT_Key IN (SELECT DPT_Key FROM link_PatInventors WHERE PN_Key IN (7,470))) AND (DPT_Key IN (SELECT DPT_Key FROM link_PatAssignees WHERE AS_Key IN (5))) AND (DPT_AppFilingDate >= '02/01/1998' AND DPT_AppFilingDate <= '12/31/2007') ) )ORDER BY ct.RANK DESC, d.DOC_Key DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources