Duplicate recs in SQL

[Steven Bell]

Just wondering if there is a SQL guru out there that might be able to help.

I have a SQL statement that is roughly.

SELECT 'several columns one column has sum'
FROM 'many tables with inner joins'  
     (T312.CODE IN ('EINA','EINE','EINV')) 
     'some more inner joins'
GROUP BY
      'same columns as in select'

The problem is the codes (EINA, EINE, and EINV) are permissions and a user can have more than one permission. If I don't have T312.CODE in the SELECT and GROUP BY the sum is multiplied by the number of permissions, if I do put the T312.CODE in the SELECT and GROUP BY I get extra rows for multiple permissions.

Is there a way to change the IS to a 'if you find one, great stop looking', also if this is possible is there a way to order how it looks (ei will find EINA before EINV or something).

Thanks.

[Paul Sturrock]

The easiest way is 3 seperate queries.

[Jeanne Boyarsky]

Originally posted by Steven Bell:
Is there a way to change the IS to a 'if you find one, great stop looking', also if this is possible is there a way to order how it looks (ei will find EINA before EINV or something).

No. The best you could do is sort and limit the number of rows returned. But this wouldn't stop the database from performing the whole query. I agree with Paul that you would be better served with separate queries.

[Steven Bell]

Thanks, I was able to split it up into two seperate queries.