SELECT problem - 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)

SELECT problem

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-04 : 06:31:26

Hello,
I have:


tbl_link
id
id_mail
id_person

id   id_mail   id_person
18     14          205
19     15          205
20     16          26
21     17          205
22     18          205

tbl_email
id
actionCode

id      actionCode
14      Christmas card
15      Easter card
16      Christmas card
17      Discount action
18      Summer sale

This is to make a mailing to a selected group of people.
Now I want to make a selection. I want everyone how has not received a "summer sale". So the result is id 26. Now I want everyone how has not received "Christmas card". So the result is NOTTHING.

Thanxx
Bjorn

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-04 : 06:37:15

SELECT
id_person
FROM
tbl_link a
LEFT OUTER JOIN
(
SELECT id from tbl_email
WHERE actionCode = 'Christmas card'
) b
ON b.[id] = a.id_mail
WHERE b.id is null

Hmm, on second thoughts I don't think that will work, but it's a starting point.

-------
Moo.

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-04 : 06:51:40

quote:

SELECT
id_person
FROM
tbl_link a
LEFT OUTER JOIN
(
SELECT id from tbl_email
WHERE actionCode = 'Christmas card'
) b
ON b.[id] = a.id_mail
WHERE b.id is null

Hmm, on second thoughts I don't think that will work, but it's a starting point.

-------
Moo.

Nop The statement is not going to work.
Anyone else?

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-04 : 08:16:30

SELECT id_person
FROM tbl_link
left outer join
(
SELECT
id_person
FROM
tbl_link a
INNER JOIN
(
SELECT id from tbl_email
WHERE actionCode = 'Christmas card'
) b
ON b.[id] = a.id_mail
) c
on c.id_person = tbl_link.id_person
and c.id_person is null

or

SELECT id_person
FROM tbl_link
where id_person not in
(
SELECT
id_person
FROM
tbl_link a
INNER JOIN
(
SELECT id from tbl_email
WHERE actionCode = 'Christmas card'
) b
ON b.[id] = a.id_mail
)

-------
Moo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources