How to make this simple? - 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 2000 Forums

SQL Server Development (2000)

How to make this simple?

Author

Topic

Incognito
Starting Member

49 Posts

Posted - 2003-02-24 : 09:33:47

Hello,

I'm using this wonderfull query

. But I think I can do much better then this:

Select ContactID From ContactMailing
Where

MailingID = 2
and ContactID IN (Select ContactID From ContactMailing Where MailingID = 6)
and ContactID IN (Select ContactID From ContactMailing Where MailingID = 8)

I tried IN, = ALL but nothing worked.

Can somenone give me some tips?

Thnx all,

Gokhan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-24 : 09:53:02

quote:

Select ContactID From ContactMailing
Where
MailingID = 2
and ContactID IN (Select ContactID From ContactMailing Where MailingID = 6)
and ContactID IN (Select ContactID From ContactMailing Where MailingID = 8)

Not sure what you are trying to return here. Do you want all contactIDs that have a mailingID of 2 and 6 and 8 ??

If so, you might try:


SELECT 
  C.* 
FROM
  ContactMailing C
INNER JOIN
 (SELECT ContactID FROM
  ContactMailing WHERE MailingID in (2,6,8)
  GROUP BY ContactID 
  HAVING COUNT(*) = 3) B
ON C.ContactID = B.ContactID

- Jeff

Incognito
Starting Member

49 Posts

Posted - 2003-02-25 : 04:23:21

Thnx for the answer.

When I use "IN" it acts like "OR"
What I need is "AND". Is there an alternative for "IN" ?

All contactIDs that have a mailingID of 2 and 6 and 8 is exactly need.

Thnx again !

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-25 : 07:31:00

A little tweak should do it:

SELECT C.* FROM ContactMailing C
INNER JOIN
(SELECT ContactID FROM
ContactMailing WHERE MailingID in (2,6,8)
GROUP BY ContactID
HAVING COUNT(DISTINCT MailingID) = 3) B
ON C.ContactID = B.ContactID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-25 : 07:44:43

My solution didn't work for him? Seemed to ...

Though you are right, if the primary key of the table isn't (ContactID, MailingID) then you would definitely need the COUNT(DISTINCT) function.

COUNT(*) is a little more efficient if you can use it instead, though.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-25 : 09:10:22

ContactIDs that are part of all 3 mailings. I like the inner join .

Select A.ContactID
FROM (
(
SELECT ContactID
FROM ContactMailing
WHERE MailingID=2
) A
INNER JOIN
(
SELECT ContactID
FROM ContactMailing
WHERE MailingID=6
) B on B.ContactID=A.ContactID
INNER JOIN
(
SELECT ContactID
FROM ContactMailing
WHERE MailingID=8
) C on C.ContactID=A.ContactID
) D

Sam

Actually, Rob's query looks better to me.

Edited by - SamC on 02/25/2003 09:11:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources