SQL Question

tamamakama · #1 (**permalink**) 08-22-04, 17:04

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!

r937 · #2 (**permalink**) 08-22-04, 20:51

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

Internet Services

Web Development

Digital Marketing

Consumer Tech