Results 1 to 4 of 4

Thread: [RESOLVED] SQL - Select where both conditions true

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Resolved [RESOLVED] SQL - Select where both conditions true

    Hi
    I searched and tried a lots of things but I cannot figured this out.
    this is my table:

    Name:  4.jpg
Views: 138
Size:  37.2 KB

    and I need that the query returns A.

    Can someone please help me here?

    this is the last thing I tried:

    Code:
    SELECT D.Items
    FROM DV_Arena_ListItems L 
    JOIN DV_Arena_ListItems D 
    ON L.Items = D.Items 
    WHERE 
    L.ListItemType = 'Assigned Role' And
    L.ActiveStatus = 'Yes' And D.ActiveStatus = 'Yes' 
    GROUP BY
    D.Items
    HAVING COUNT(1) = 1

    Thanks.
    Last edited by Grand; Oct 7th, 2021 at 06:59 AM.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: SQL - Select where either but not both has values

    Your Query is not corresponding with the sample data.....
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL - Select where either but not both has values

    Is it better now? I changed the table structure.

    Edit:

    Explanation/correction: OK, I see now. I actually asked in my first post the opposite of what I need
    I need that the query gives me the list of items that have both null and ICRelated for Info column.

    Sorry about the confusion.

    Edit 2:
    I think I got it:
    Code:
    SELECT L.Items 
    FROM DV_Arena_ListItems L 
    Where 
    L.ListItemType = 'Assigned Role' And
    L.ActiveStatus = 'Yes' 
    GROUP BY L.Items 
    -- Order by L.Items  ASC
    HAVING COUNT(L.Items) = 2
    Last edited by Grand; Oct 7th, 2021 at 06:58 AM.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: SQL - Select where both conditions true

    So, basically for the sample data, you want Items A and B returned?
    Code:
    SELECT * 
    FROM 
    Test T1 
    INNER JOIN 
    test T2
    ON 
    T1.Items=T2.Items 
    WHERE 
    T1.Info Is Null And T2.Info='ICRelated'
    Returns:

    Items ItemName Info ActiveStatus ListItemType Items ItemName Info ActiveStatus ListItemType
    A Admin Yes AssignedRole A Observer ICRelated Yes AssignedRole
    B Coordinator Yes AssignedRole B Coordinator ICRelated No AssignedRole

    Funny enough: Item A has 2 ItemName
    Last edited by Zvoni; Oct 7th, 2021 at 09:28 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width