locked
Select certain rows but not others RRS feed

  • Question

  • I have a table, as below, which i'm trying to extract data from


    Accountid Accountidname Product
    1 One IP
    1 One GL
    1 One MI
    2 Two IP
    2 Two GL
    3 Three MI
    3 Three PA
    3 Three CI
    4 Four CI
    4 Four IP

    What I want to be able to do is only show accounts that contain certain products but not others. For example accounts that have IP or GL but not MI. Using this example I would only see accounts 2 and 4.

    I'm struggling to eliminate the accounts that have MI, Is this possible?

    Thanks in advance

    Thursday, June 9, 2016 3:48 PM

Answers

  • Filter Accountid if it exist for some product and also not for others

    SELECT DISTINCT Accountid, AccountidName
    FROM YourTable
    WHERE Accountid IN (SELECT Accountid FROM YourTable WHERE Product IN ('IP', 'GL'))
          AND NOT Accountid IN (SELECT Accountid FROM YourTable WHERE Product IN ('MI'))


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 9, 2016 4:59 PM

All replies

  • Filter Accountid if it exist for some product and also not for others

    SELECT DISTINCT Accountid, AccountidName
    FROM YourTable
    WHERE Accountid IN (SELECT Accountid FROM YourTable WHERE Product IN ('IP', 'GL'))
          AND NOT Accountid IN (SELECT Accountid FROM YourTable WHERE Product IN ('MI'))


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 9, 2016 4:59 PM
  • Thanks for this Olaf, I'd been concentrating on filtering on the product so hadn't even considered filtering the accounted. I'll give it a try when back in work tomorrow!
    Thursday, June 9, 2016 8:42 PM