Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Cool Unanswered: Multiple columns in select where in ?

    can i do something like the following?

    select * from mytable
    where field1, field2 in
    (select field1, field2 from mytable
    group by field1, field2
    having count(*) = 1)

    Any help is highly appreciated!
    Last edited by peter42; 03-22-12 at 07:13.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    The query you posted is not supported, but somthing like this is:
    select * from mytable
    where field1 in
    (select field1 from mytable
    group by field1, field2
    having count(*) = 1)
    AND field2 in
    (select field2 from mytable
    group by field1, field2
    having count(*) = 1)
    2 whereclauses and a slightly different subselect.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Mar 2012
    Posts
    2
    thanks Martijn, but this would result in a wrong resultset. Your query disregards the combination of the fields.

    Try this and you will see:
    create table mytable (field1 int , field2 int )

    insert into mytable (field1, field2) values (1, 100);
    insert into mytable (field1, field2) values (1, 102);
    insert into mytable (field1, field2) values (1, 102);
    insert into mytable (field1, field2) values (1, 103);
    insert into mytable (field1, field2) values (1, 103);
    insert into mytable (field1, field2) values (2, 102);
    insert into mytable (field1, field2) values (3, 103);
    insert into mytable (field1, field2) values (4, 103);

    select * from mytable
    where field1 in
    (select field1 from mytable
    group by field1, field2
    having count(*) = 1)
    AND field2 in
    (select field2 from mytable
    group by field1, field2
    having count(*) = 1)

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Good point, I missed that part. Have to think about it a bit more...
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select * from mytable m
    where exists 
    (select 1 from mytable t
     where  m.field1=t.field1
       and  m.field2=t.field2
     group by field1, field2 
     having count(*) = 1
    )

Posting Permissions

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