Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Duplicate recs in SQL

 
Steven Bell
Ranch Hand
Posts: 1071
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just wondering if there is a SQL guru out there that might be able to help.

I have a SQL statement that is roughly.

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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The easiest way is 3 seperate queries.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1071
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, I was able to split it up into two seperate queries.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic