1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL Condition - alternative to intersect?

Discussion in 'Databases' started by Yo-Yo32, Apr 13, 2008.

  1. #1
    Hi guys n gals,

    I was wondering if someone might have a neat solution for this...

    Let's say I have a table like this:

    [MyTable]
    id entry
    -----------------------
    1 Entry A
    4 Entry B
    5 Entry C
    2 Entry A
    2 Entry D

    and I want to select the entries from MyTable that have an id and of 1 and 2. I would expect there to be one result: "Entry A". Any ideas how I can do it?

    I thought I could do it with an intersect, but MS Access doesn't support intersect statements. Also (just to make things trickier), it should be scable, in that I may wish to only display the entries that have 20 id's - so I'd like to avoid whopping great nested SQL statments for each of the 20 ids.

    Any ideas would be greatfully received!
     
    Yo-Yo32, Apr 13, 2008 IP
  2. CreativeClans

    CreativeClans Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Do you want all entries that have 2 id's, or only the ones that have the id's 1 and 2 (excluding for example an entry with id 1 and 4) ?
     
    CreativeClans, Apr 14, 2008 IP
  3. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Think you are simply looking for the unique entries in the entry column where the id is within a certain list of values?

    Surely you would just use something like SELECT DISTINCT Entry FROM MyTable WHERE ID = 1 ?
     
    AstarothSolutions, Apr 14, 2008 IP
  4. vpguy

    vpguy Guest

    Messages:
    275
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I'm not sure if it's possible to create a scalable statement which would allow for an abitrary number of unique ID's.

    The following statement works when there are two ID's (specifically, 1 and 2):

    SELECT Entry FROM MyTable WHERE (ID=1) AND (Entry IN (SELECT Entry FROM MyTable WHERE ID=2));
    Code (markup):
    I'm no SQL expert though. The solution you seek might be possible.
     
    vpguy, Apr 14, 2008 IP
  5. CreativeClans

    CreativeClans Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #5
    This might work (didn't test it though) to get all entries that have all specified id's:
    
    SELECT entry
    FROM tablename
    WHERE id IN (1, 2)
    GROUP BY entry
    HAVING COUNT(*) = 2
    
    Code (markup):
    You can dynamically create the list of id's for the IN statement, and put the number of id's in the COUNT statement.

    If you want to get all entries that have a certain number of id's (but you don't care which id's), then just take out the IN statement:
    
    SELECT entry
    FROM tablename
    GROUP BY entry
    HAVING COUNT(*) = 2
    
    Code (markup):
     
    CreativeClans, Apr 15, 2008 IP
    vpguy likes this.
  6. vpguy

    vpguy Guest

    Messages:
    275
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Brilliant SQL statements - I believe you figured out the solution they were looking for.
     
    vpguy, Apr 15, 2008 IP