Apply And operator on same column

Question

Apply And operator on same column

shirish1987

Right there with Babe

Points: 776
More actions
June 11, 2015 at 2:20 am

#318191

Hi,
I have one table NewsEntities which contains one to many relationship.ie. One NewsId can have multiple EntityId.
Consider,NewsId=1 has two entities with EntityId 1 and EntityId 2 .there multiple newsid which can same or different EntityId
Now i want to find those news in which EntityId=1 is Present but EntityId=2 is absent
kindly provide solution.
Thanx and Regards
Shirish Phadnis

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23000 More actions · Answer 1

shirish1987 (6/11/2015)
Hi,
I have one table NewsEntities which contains one to many relationship.ie. One NewsId can have multiple EntityId.
Consider,NewsId=1 has two entities with EntityId 1 and EntityId 2 .there multiple newsid which can same or different EntityId
Now i want to find those news in which EntityId=1 is Present but EntityId=2 is absent
kindly provide solution.
Thanx and Regards
Shirish Phadnis

If you could post some sample data it will be much easier to give you an answer. I think I know what you mean but I'm not sure.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 2

Are you after something like this?

SELECT DISTINCT ne.NewsID

FROM NewsEntities ne

LEFT JOIN (SELECT DISTINCT NewsID FROM NewsEntities WHERE EntityID = 2) tne ON tne.NewsID = ne.NewsID

WHERE ne.EntityID = 1

AND tne.NewsID IS NULL

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

CREATE TABLE #NewsEntities (NewsEntity INT NOT NULL identity(1,1), NewsId INT, EntityId INT)

INSERT INTO #NewsEntities (NewsId, EntityId)

VALUES

(1,1),(1,2),(1,3),

(2,1),(2,5),(2,3),

(3,5),(3,2),(3,3)

SELECT n.NewsId

FROM #NewsEntities n

WHERE EntityId = 1

AND NOT EXISTS (

SELECT 1

FROM #NewsEntities ni

WHERE ni.NewsId = n.NewsId AND ni.EntityId = 2)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

shirish1987 Right there with Babe Points: 776 More actions · Answer 4

Consider following data

NewsId EntityId

1 1

1 2

2 1

3 2

So I want those news which has EntityId=1 is assigned but not EntityId=2

shirish1987 Right there with Babe Points: 776 More actions · Answer 5

Thanx all for your solution.

Thanx and Regards

Shirish Phadnis

David Burrows SSC Guru Points: 65142 More actions · Answer 6

Bit late 😀

SELECT NewsId

FROM #NewsEntities

WHERE EntityId IN (1,2)

GROUP BY NewsId

HAVING SUM(EntityId) = 1

Far away is close at hand in the images of elsewhere.
Anon.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Do you mind if I join the party?

SELECT n.NewsId

FROM #NewsEntities n

WHERE EntityId = 1

EXCEPT

SELECT n.NewsId

FROM #NewsEntities n

WHERE EntityId = 2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2