Post reply

Apply And operator on same column

  • 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

  • June 11, 2015 at 2:40 am

    #1804612

    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

  • June 11, 2015 at 2:56 am

    #1804616

    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

    LinkedIn

  • June 11, 2015 at 3:16 am

    #1804624

    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

  • June 11, 2015 at 3:22 am

    #1804625

    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

  • June 11, 2015 at 3:26 am

    #1804627

    Thanx all for your solution.

    Thanx and Regards

    Shirish Phadnis

  • June 11, 2015 at 6:52 am

    #1804660

    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.

  • June 11, 2015 at 9:01 am

    #1804727

    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

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

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