Double where select - 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)

Double where select

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2009-07-27 : 04:33:47

Hi All,

i got this select statement:

SELECT id, articleid, comment, username, useremail, lang, active, ' ' AS translate
FROM dbo.T_comments
WHERE (lang = 'en') AND (NOT (articleid IN
(SELECT articleid
FROM dbo.T_comments AS T_comments_1
WHERE (lang = 'es'))))

but i need to add another field to the filter, so it compares if the row doesnt have those 2 values.
Something like this, but doesn't work

SELECT id, articleid, comment, username, useremail, lang, active, ' ' AS translate
FROM dbo.T_comments
WHERE (lang = 'en') AND (NOT (articleid and USEREMAIL IN
(SELECT articleid,USEREMAIL
FROM dbo.T_comments AS T_comments_1
WHERE (lang = 'es'))))

Any idea how to do this?

thanks a lot
Mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 04:49:47

[code]
SELECT id,
articleid,
comment,
username,
useremail,
lang,
active,
' ' AS translate
FROM dbo.T_comments c
WHERE (lang = 'en')
AND NOT EXISTS
(
SELECT *
FROM dbo.T_comments x
WHERE x.lang = 'es'
ADN x.articleid = c.articleid
AND x.USEREMAIL = c.USEREMAIL
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mike13
Posting Yak Master

219 Posts

Posted - 2009-07-27 : 09:50:29

That worked, thank a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources