AND's or OR's - 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

SQL Server Development (2000)

AND's or OR's

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-01-10 : 10:15:07

I have the following code which is supposed to return documents for only that persons id (5759)

What I am trying to say on my selection criteria is that if any and not all of those have TEST in it select it but only if it is associated to the id of 5759

What I am getting at the moment is that it works for the first two select (client_ref and title) but not est_id which is an int.


select status_desc, title, client_ref,(cl_contact_fname + ' ' + cl_contact_lname) as contact_name,
		est_datetime,e.est_id from 
		tblEstimate e,tblEstStatus es,tblclientcontact cc
			where e.status_id=es.status_id and cc.cl_contact_id=cc_id
		and 
			(cc_id='5759' or cc_id in 
				(select cl_contact_id_2 
				from tblClientContactViewContact 
				where cl_contact_id_1='5759') 
			) 
		and 
		e.status_id in (1,5)
				
		and (client_ref like '%' + 'Test' + '%') or (title like '%' + 'Test' + '%') or (est_id like '%' + 'Test' + '%')	
		and (est_datetime >= '01-jan-1980' or est_datetime is NULL)
		
	order by est_datetime

any ideas?

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-10 : 10:30:51

some here may be able to figure this out but you would be helping if you would
1.provide the table alias for each selected value
2.provide sample data for the 4 tables with a desired result set

you could probably get it yourself once you understand this
http://www.databasejournal.com/features/mssql/article.php/1438001

I wish someone would start an Official XML Rant Thread.

jhermiz

3564 Posts

Posted - 2005-01-10 : 10:31:43

Before I look at your code, check out your first AND condition:


		and 
			(cc_id='5759' or cc_id in 
				(select cl_contact_id_2 
				from tblClientContactViewContact 
				where cl_contact_id_1='5759') 
			)

Notice the OR in there...think boolean logic.

1 OR 0 = 1 (true)
1 OR 1 = 1 (True)
0 Or 0 = 0 (false
1 AND 0 = 0 (FALSE)
0 AND 0 = 0 (False)
1 AND 1 = 1 (True)

Your query will still return results because of this first AND / OR condition...look over it you are saying the following

IF the id is 5759 OR some other condition then return me the results.
What happens here is say ID is not 5759 say it is 5758. The first portion returns
0 which is good, however the second expression returns true. That makes 0 OR 1 which
implies TRUE. This will not return the right results because your ID did not meet the criteria.

This should help you come up with a solution...Use paper and pencil to help you figure this.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

jparker
Posting Yak Master

118 Posts

Posted - 2005-01-10 : 10:49:59

Cheers guys I have actually realised all I needed to do was enclose the conditions in one parentheses

(client_ref like '%' + 'Test' + '%' or title like '%' + 'Test' + '%' or est_id like '%' + 'Test' + '%')

sorry to waste your time it was a silly error on my behalf

Have a good day

jhermiz

3564 Posts

Posted - 2005-01-10 : 11:12:40

Doing that definately changes the outcome :) good job.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-10 : 11:21:17

If you wrote the original query, revisit this when you get the time,
and check out the ANSI join syntax.
Missing brackets or complex WHERE clauses can
cause all kinds of logic failures, exploiting ANSI joins can help
divide and conquer the logic so "the bracket drop battle" occurences are less.
I am glad it was easy to fix and you aren't stuck.

I wish someone would start an Official XML Rant Thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources