help with two related where clauses

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 2005 Forums

Transact-SQL (2005)

help with two related where clauses

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-21 : 16:54:18

I have a couple of queries in a union all that need to get all records, some from the first where clause, and everything else in the other.

Here is the original where clause:
from license_status ls, pos_driver pd, pos_policy pp
where pp.pos_id = @pos_id
and pd.pos_id = pp.pos_id
and pd.driver_number = @driver
and ls.company = pp.company
and ls.license_state = pd.license_state

I have added a few additional elements to limit records to the state of washington, for a particular company, and a specific answer to a question.

Here is the new one:
from license_status ls, pos_driver pd, pos_policy pp, pos_answers pa
where pp.pos_id = @pos_id
and pp.policy_state = 'WA'
and pp.company = 5
and pd.pos_id = pp.pos_id
and pd.driver_number = @driver
and ls.company = pp.company
and ls.license_state = pd.license_state
and pp.pos_id = pa.pos_id
and pa.question_number = 8
and pa.yes_no = 'YES'

So now I need to rewrite the original where clause to get all the records this new where clause excludes. I started by adding this:
and pp.policy_state = 'WA'
But realized there would be some policies in WA that did not meet the other criteria.

So how do I go about this without writing a ton of permutations to get all the possibilities?

Thanks!

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2009-05-21 : 19:24:30

Why would you add pp.policy_state = 'WA' to the original WHERE clause if you are trying to get records that are excluded by the new WHERE clause?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-21 : 19:37:16

Your query is only good for inner joins. Break the habit of FROM table1,table2,Table3 and replace with Table1 INNER JOIN Table2 INNER JOIN Table3. What you want is a LEFT JOIN or a NOT EXISTS clause.
Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:41:59

your explanation does not make much sense, can you post what exactly is your requirements. i didnt seem to understand need of having so many permutations. are you looking for conditional filter type logic?

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-22 : 09:33:36

quote:
Originally posted by influent

Why would you add pp.policy_state = 'WA' to the original WHERE clause if you are trying to get records that are excluded by the new WHERE clause?

My mistake, that whould be <> not =.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-22 : 09:36:21

quote:
Originally posted by visakh16

your explanation does not make much sense, can you post what exactly is your requirements. i didnt seem to understand need of having so many permutations. are you looking for conditional filter type logic?

The stored procedure using this query is building an XML document using "for xml explicit".

One of three queries in a "union all" query is selecting two cloumns. When the conditions I specified are true(WA, question_number = 8, etc), we want to use a hardcoded value for one of the columns in the select statement. Otherwise, use the value from the db.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 13:52:21

then why dont you use a case ...when expression?

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-22 : 14:17:39

quote:
Originally posted by visakh16

then why dont you use a case ...when expression?

Can I put another select within the case statement? Tried to find an example of that on here in the articles but couldn't find it.

Any example would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 14:23:42

why you need to put select inside case? what you told was you need a field from db or constant. do you mean you need a subquery for that?

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2009-05-22 : 14:27:00

This should help: http://ottoradke.com/t-sql-case-statement-based-on-a-subquery/

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-22 : 14:28:16