Help with SQL script

[slingblade]

There must be a way to do what I want to do without using a cursor, but I can't think of it. FYI, I'm a marketing analyst and not all that good with sql.

Here's the scenario:

SITE table

ID  NAME          AD1          CITY          STATE  MONTHLY
1   JOHN DOE      11 MAIN ST   BIGCITY       FL     30.00
2   JANE DOE      22 MAIN ST   BIGCITY       FL     25.00
3   BOB SMITH     33 MAIN ST   BIGCITY       FL     32.00
4   JOE JONES     44 MAIN ST   SMALLTOWN     FL     27.00
5   MIKE COOPER   55 MAIN ST   BIGCITY       FL     40.00
6   MIKE COOPER   55 MAIN ST   SUBURB        FL      0.00
7   JIM ANDREWS   66 MAIN ST   BIGCITY       FL     45.00
8   JIM ANDREWS   66 MAIN ST   BIGCITY       FL      0.00
9   LISA LOCKE    77 MAIN ST   SMALLTOWN     FL      0.00
 

What I'm trying to do is get a list from the SITE table, but I don't want do a select *.

Do you see ID rows 5-8? The name and address duplicate, but the city name may not (but they usually match, or one is a suburb of the other.) The main thing is that the monthly amount is always zero for one of the records.

Notice in record 9 (ID value) that a person may have one "address" row, but still have zero for the monthly.

What I need is a list that pulls all the recs in SITE, EXCEPT leaving out any with zero amounts IF AND ONLY IF there are two records and one is zero monthly and the other is GT zero.

Such a select would return to me ID rows 1,2,3,4,5,7,9. ID rows 6 and 8 would be omitted because of the duplicate name and address. Row 9 would be included in the list because the monthly is zero, yes, but there is no matching record.

Is there any way to do this without a cursor?

Any help is greatly appreciated! Thanks!

[Rabbit]

Duplicates are always 0? There's never a case where it's not zero?

[slingblade]

The duplicate records ALWAYS have the same name, the same street address, but different monthly amounts. The monthly amount on the dupe record is always zero, no exception.

There could be other zero monthly amounts, but there wouldn't be duplicate name/address records for those, as shown by ID record 9 in my example table. So I can't just say, "select * from site where monthly <> 0". I have to have some kind of name and/or address comparison.

[Rabbit]

Try something like this, you might have to tweak it a little bit.

SELECT *
FROM [SITE]
WHERE [ID] IN (
    SELECT T1.[ID]
    FROM [SITE] T1, [SITE] T2
    WHERE T1.[NAME] = T2.[NAME]
        AND T1.AD1 = T2.AD1
        AND T1.MONTHLY <= T2.MONTHLY
    GROUP BY T1.[ID], T1.[NAME], T1.AD1
    HAVING COUNT(*) = 1
)

[slingblade]

Awesome! Thanks Rabbit!