Unruly monster query. Help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Unruly monster query. Help!

Author

Topic

beady
Starting Member

28 Posts

Posted - 2007-02-22 : 19:41:29

The code of the SP which is causing me a lot of pain at the moment is shown below. The context for this query is an asp.net form where a user can enter any or no query parameters. I am incrementally building and testing the query and all has been working fine until I came to the code shown in bold.

What happens when I add those pieces of code, is that it suddenly no longer shows all other records, but only a specific Case_Entity ID which is in the REF_CE_DEF_MA table, and not any of the others that are also in that table or in the other tables referenced in that query.

With this kind of query, very broad, I would expect more results, not fewer.

What I have done wrong?

@idCOURT integer = Null,
@idJUDGE integer = Null,
@idADMIN_PROCEEDING_TYPE integer = Null,
@idADMIN_PROCEDURAL_ISSUES integer = Null,
@idADMIN_REVIEW_TYPE integer = Null,
@idREF_DEFINITION_TYPE integer = Null,
@citation_date1 datetime = 'January 1, 1753',
@citation_date2 datetime = 'December 31, 9999'
AS
SET NOCOUNT ON

SELECT
*,
st.description AS legal_status,
Outcome.description AS outcome_description,
cmp.description As court_composition,
ADMIN_PROCEEDING_TYPE.description AS [Proceeding Type],
ADMIN_PROCEEDING_TYPE.idADMIN_PROCEEDING_TYPE AS ProcTypeID,
ADMIN_PROCEDURAL_ISSUES.description AS [Procedural Issues],
ADMIN_PROCEDURAL_ISSUES.idADMIN_PROCEDURAL_ISSUES AS IssuesID,
ADMIN_CE_REVIEW_GROUNDS.[section] AS [Rev Grnds Section],
ADMIN_REVIEW_DEFINITION.description AS [Review Defn],
ADMIN_REVIEW_TYPE.description AS [Review Descn],
REF_DEFINITION_TYPE.description AS [REF TYPE DESCN],
REF_DEFINITION.description AS [REF DEF DESCN],
REF_CE_DEF_MA.[section] AS [MA SECTION],

0 AS ForAgainst

FROM CASE_ENTITY ce
INNER JOIN COURT co ON co.idCOURT = ce.idCOURT
INNER JOIN FOR_VOTE ON ce.idCASE_ENTITY = FOR_VOTE.idCASE_ENTITY
INNER JOIN JUDGE jdg ON FOR_VOTE.idJudge = jdg.idJUDGE
INNER JOIN COMPOSITION cmp ON ce.idCOMPOSITION = cmp.idCOMPOSITION
INNER JOIN LEGAL_STATUS st ON ce.idLEGAL_STATUS = st.idLEGAL_STATUS
INNER JOIN OUTCOME ON ce.idOUTCOME = OUTCOME.idOUTCOME
INNER JOIN ADMIN_CE_PROC_TYPE ON ce.idCASE_ENTITY = ADMIN_CE_PROC_TYPE.idCASE_ENTITY
INNER JOIN ADMIN_PROCEEDING_TYPE ON ADMIN_CE_PROC_TYPE.idADMIN_PROCEEDING_TYPE = ADMIN_PROCEEDING_TYPE.idADMIN_PROCEEDING_TYPE
INNER JOIN ADMIN_CE_ISSUES ON ce.idCASE_ENTITY = ADMIN_CE_ISSUES.idCASE_ENTITY
INNER JOIN ADMIN_PROCEDURAL_ISSUES ON ADMIN_CE_ISSUES.idPROC_ISSUES = ADMIN_PROCEDURAL_ISSUES.idADMIN_PROCEDURAL_ISSUES
INNER JOIN ADMIN_CE_REVIEW_GROUNDS ON ce.idCASE_ENTITY = ADMIN_CE_REVIEW_GROUNDS.idCASE_ENTITY
INNER JOIN ADMIN_REVIEW_DEFINITION ON ADMIN_CE_REVIEW_GROUNDS.idADMIN_REVIEW_DEF = ADMIN_REVIEW_DEFINITION.idADMIN_REV_DEF
INNER JOIN ADMIN_REVIEW_TYPE ON ADMIN_REVIEW_DEFINITION.idADMIN_REV_TYPE = ADMIN_REVIEW_TYPE.idADMIN_REVIEW_TYPE
INNER JOIN REF_CE_DEF_MA ON ce.idCASE_ENTITY = REF_CE_DEF_MA.idCASE_ENTITY
INNER JOIN REF_DEFINITION ON REF_CE_DEF_MA.idREF_DEF = REF_DEFINITION.idREF_DEFINITION
INNER JOIN REF_DEFINITION_TYPE ON REF_DEFINITION.idRef_Def_Type = REF_DEFINITION_TYPE.idREF_DEFINITION_TYPE
WHERE
((ce.idCOURT = @idCOURT) OR(@idCOURT IS NULL) )
AND((jdg.idJUDGE =@idJUDGE) OR(@idJUDGE IS NULL))
AND citation_date BETWEEN @citation_date1 AND @citation_date2
AND((ADMIN_PROCEEDING_TYPE.idADMIN_PROCEEDING_TYPE = @idADMIN_PROCEEDING_TYPE)
OR (@idADMIN_PROCEEDING_TYPE IS NULL))
AND((ADMIN_PROCEDURAL_ISSUES.idADMIN_PROCEDURAL_ISSUES = @idADMIN_PROCEDURAL_ISSUES)
OR (@idADMIN_PROCEDURAL_ISSUES IS NULL))
AND(( ADMIN_REVIEW_TYPE.idADMIN_REVIEW_TYPE = @idADMIN_REVIEW_TYPE)
OR (@idADMIN_REVIEW_TYPE IS NULL))
AND((REF_DEFINITION_TYPE.idREF_DEFINITION_TYPE = @idREF_DEFINITION_TYPE)
OR (@idREF_DEFINITION_TYPE IS NULL))

ORDER BY ForAgainst,
ce.citation_date desc

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-22 : 21:13:29

Try changing the inner joins to left outer joins on the joins you have added.
Sounds like the link isn't always there so you need to exclude the filter when the criteria isn't input.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-22 : 21:18:58

What's wrong then?

Since you are inner joining CASE_ENTITY with REF_CE_DEF_MA, you will only get those records where idCASE_ENTITY matches in both the tables.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

beady
Starting Member

28 Posts

Posted - 2007-02-22 : 21:52:08

quote:
Originally posted by nr

Try changing the inner joins to left outer joins on the joins you have added.
Sounds like the link isn't always there so you need to exclude the filter when the criteria isn't input.

That looks like it. Thanks a million!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources