 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Services / Reporting Services / July 2008Getting duplicate records because of Event_Code
If I take out the second half of the WHERE clause I do not get current inmates that have no DISC date. With the second half in the WHERE clause I get duplicate records. WHERE (INMATE_NAMES.SEQ_NUM = 1) AND (INMATE_IDENT.SEQ_NUM = 1 OR INMATE_IDENT.SEQ_NUM IS NULL) AND (INMATE_NAMES.LAST_NAME LIKE @LASTNAME) AND (EVENTS.EVENT_CODE = N'BOOK') AND (EVENTS_1.EVENT_CODE = N'DISC') AND (INMATE_NAMES.FIRST_NAME LIKE @FIRSTNAME) OR (INMATE_NAMES.SEQ_NUM = 1) AND (INMATE_IDENT.SEQ_NUM = 1 OR INMATE_IDENT.SEQ_NUM IS NULL) AND (INMATE_NAMES.LAST_NAME LIKE @LASTNAME) AND (EVENTS.EVENT_CODE = N'BOOK') AND (EVENTS_1.EVENT_CODE = N'DISC') AND (@FIRSTNAME IS NULL) OR (INMATE_NAMES.SEQ_NUM = 1) AND (INMATE_IDENT.SEQ_NUM = 1 OR INMATE_IDENT.SEQ_NUM IS NULL) AND (INMATE_NAMES.LAST_NAME LIKE @LASTNAME) AND (EVENTS.EVENT_CODE = N'BOOK') AND (INMATE_NAMES.FIRST_NAME LIKE @FIRSTNAME) OR (INMATE_NAMES.SEQ_NUM = 1) AND (INMATE_IDENT.SEQ_NUM = 1 OR INMATE_IDENT.SEQ_NUM IS NULL) AND (INMATE_NAMES.LAST_NAME LIKE @LASTNAME) AND (EVENTS.EVENT_CODE = N'BOOK') AND (@FIRSTNAME IS NULL) Any suggestions would be greatly appreciated. Thank you Frank Sure, use a select distinct to insure no duplicates.  SignatureBruce Loehle-Conger MVP SQL Server Reporting Services > If I take out the second half of the WHERE clause I do not get current > inmates that have no DISC date. With the second half in the WHERE clause [quoted text clipped - 20 lines] > > Any suggestions would be greatly appreciated. Thank you Frank Bruce, I read this article and appreciate your suggestion but am trying to adjust my WHERE clause if possible. There is an understanding in the database world that using a "SELECT DISTINCT" SQL query is not a good idea, because it is essentially getting duplicate rows out of the database and then discarding them. Usually it is better to rearrange the "WHERE" clause in the query to only get the rows you need. One of these cases came up in a 3-way join query in a PostgreSQL database I administer. This article gives a step-by-step "case study" of analyzing a query in PostgreSQL and how to ensure that your SQL rewrite is actually paying off. > Sure, use a select distinct to insure no duplicates. > [quoted text clipped - 22 lines] > > > > Any suggestions would be greatly appreciated. Thank you Frank Bruce, how do I incorporate a Select Distinct into my query? Thank you > If I take out the second half of the WHERE clause I do not get current > inmates that have no DISC date. With the second half in the WHERE clause I [quoted text clipped - 15 lines] > > Any suggestions would be greatly appreciated. Thank you Frank I wound up fixing my problem by adjusting my Grouping in Layout. Thanks for the suggestions. Frank > If I take out the second half of the WHERE clause I do not get current > inmates that have no DISC date. With the second half in the WHERE clause I [quoted text clipped - 15 lines] > > Any suggestions would be greatly appreciated. Thank you Frank |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.