Results 1 to 2 of 2

Thread: SQL Question

  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: SQL Question

    I have a sql problem. Consider this table:

    +---------+-------------+-------+-------+
    | issueid | fieldbankid | cond1 | cond2 |
    +---------+-------------+-------+-------+
    | 1 | 1 | 1 | 0 |
    | 1 | 2 | 0 | 1 |
    | 2 | 1 | 0 | 0 |
    | 2 | 2 | 0 | 0 |
    | 3 | 1 | 1 | 0 |
    | 3 | 2 | 0 | 0 |
    | 4 | 1 | 0 | 0 |
    | 4 | 2 | 0 | 0 |
    +---------+-------------+-------+-------+

    I want to pull out the issueids where cond1 and cond2
    are both > 0.

    By inspecting the data above, the answer is of course
    issueid 1.

    But I can't figure out what the sql statement to use
    would be...

    Here are the statements to build the test case:

    create table test (
    issueid int(255),
    fieldbankid int(255),
    cond1 int (255),
    cond2 int (255)
    );

    insert into test values (1, 1, 1, 0);
    insert into test values (1, 2, 0, 1);
    insert into test values (2, 1, 0, 0);
    insert into test values (2, 2, 0, 0);
    insert into test values (3, 1, 1, 0);
    insert into test values (3, 2, 0, 0);
    insert into test values (4, 1, 0, 0);
    insert into test values (4, 2, 0, 0);

    I would like to create most efficient query possible, avoiding correlated subqueries if possible. Any help would be greatly appreciated!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select issueid 
      from test
     where cond1 = 1
        or cond2 = 1
    group
        by issueid
    having count(*) = 2
    this is the simplest, and assumes there can be only two rows for each id as in your test sample

    if there can be more, you'll need slightly different sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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