Query Help! (A Simple Filtering Problem)

Hi. I need to know how I can query for rows based on the existence of other rows. I've simplified the problem down to this. Suppose I have a table with fields called ID1 and ID2, which together make up the key. ID1 can be any number and ID2 can be 0, 1 or 2. I want all rows with ID2 = 1. If there is no record with ID2 = 1, but there is a corresponding record with ID2 = 0, then return it instead. Otherwise ignore that record. I know that sounds complicated, but the idea is pretty simple. If my table looks like this,

ID1 ID2
------ ------

581 0
581 1
581 2
582 0
582 2
583 2
584 0
584 1

then my query should return

ID1 ID2
------ ------

581 1
582 0
584 1

Only one record of ID1 should be returned and its ID2 should be 1, or 0 if no record with ID2 = 1 exists.

I can't use PL/SQL and I'm trying to avoid a subquery since the actual queries I'm working with are already huge. That's what makes this difficult. Does anyone see a way to do this using pure SQL? I would sincerely appreciate any help at all.

Thanks in advance.

-jk

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 10 2001 - 07:48:52 CST