Short circuit query difficulties

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)

Short circuit query difficulties

Author

Topic

john.youngblood
Starting Member

6 Posts

Posted - 2005-09-14 : 11:42:26

Hello,

I am having problems constructing a query that will short circuit. This is what I have right now:

Line1: SELECT * FROM Table WHERE [Column1] = 'data1'
Line2: AND ([Column2] = 'data2' AND [Column3] = 'data3')
Line3: OR [Column2] = 'data2'

In other words, in Line2, I have a very specific search: I am checking Column2 and Column3. If it returns a row, I do not want to execute Line3. However, if no rows are returned from executing Line2, I want to step back to a more generic search and execute Line3.

This, unfortunately, does not work as I expected. When I run it, I receive the rows returned by evaluating Line2, and also the rows returned by evaluating Line3.

What am I doing wrong and how should I restructure this query to get my intended results?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-14 : 11:52:12

Never mix AND's and OR's without paranthesis.

Currently you have:

WHERE A and B or C

you should always explicitly indiciate if you want

WHERE (A and B) or C

or

WHERE A and (B or C)

by using parens.

john.youngblood
Starting Member

6 Posts

Posted - 2005-09-14 : 12:04:02

I have paretheses in Line2 though.

Line1: WHERE A
Line2: AND ( B AND C )
Line3: OR B

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-14 : 12:19:41

quote:
Originally posted by john.youngblood

I have paretheses in Line2 though.

Line1: WHERE A
Line2: AND ( B AND C )
Line3: OR B

Substitute the (B and C) expression with "D", and you have:

WHERE A and D or B

Which is exactly what I was describing. Just add more parens to explicitly indicate what you want.

john.youngblood
Starting Member

6 Posts

Posted - 2005-09-14 : 13:18:37

Ok, I understand what you're saying. I was forgetting that the WHERE argument needed to be factored in. I changed my query to this:

Line1: SELECT * FROM Table
Line2: WHERE ( [Column1] = 'data1' AND [Column2] = 'data2' AND [Column3] = 'data3' )
Line3: OR ( [Column1] = 'data1' AND [Column2] = 'data2' )

However, even though Line2 got results back, it's still giving me the results from Line3. Do I still not have enough parentheses?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-14 : 13:58:32

It might be helpful if you can provide sample data and your expected results.

john.youngblood
Starting Member

6 Posts

Posted - 2005-09-14 : 14:25:30

Ok, this a few of the rows from my database: ( sorry about the formatting )

top: Column1 | Column2 | Column3

row1: data1 | data2 | data3
row2: data1 | data2 | notdata3

So, I'm expecting to get row1 as my only return, as it contains data3 in Column3, and that is what I'm checking for first in my query. I get, however, both rows, showing that the second part of my query was executed, looking only at Column1 and Column2.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-14 : 14:27:24

SELECT returns all rows that match the WHERE clause, unless you specify "TOP":

SELECT TOP 1 * FROM ... WHERE ...

will return only 1 row.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-14 : 14:34:53

John,

You need to provide some more data, as what you have provided can be solved with this:

SELECT Column1, Column2, Column3
FROM Table1
WHERE Column3 <> 'notdata3'

OR this:

SELECT Column1, Column2, Column3
FROM Table1
WHERE Column3 = 'data3'

Your question, I'm sure, is more complicated, so the sample data that you provide needs to reflect this in order for us to help you.

Tara

john.youngblood
Starting Member

6 Posts

Posted - 2005-09-14 : 15:06:45

Ok, this is how my database looks,

_______________________________
ID | Code | Description | Type
-------------------------------
ME | 0100 | one descrip | 1
ME | 0100 | diff descrip |
ML | 0136 | description | 7

And this is my query:

SELECT * FROM Table
WHERE ( [ID] = 'ME' AND [Code] = '0100' AND [Type] = '1' )
OR ( [ID] = 'ME' AND [Code] = '0100' )

My intention is to first try and get, out of the database, the row that is qualified by matching up with my data in the [ID], [Code], and [Type] columns.

If, however, I don't get a result from that stringent of a search, I want to relax my search by making it a bit more general. I would do this by removing the [Type] column check.

In all cases, I would only want to get back one result. However, with the way that I've phrased this query, I am getting back both results.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-14 : 15:20:20

Try this:


CREATE TABLE Table1 
(ID char(2) NOT NULL, Code varchar(10) NOT NULL, Description varchar(50) NOT NULL, Type char(1))

INSERT INTO Table1 VALUES('ME', '0100', 'one descrip', '1')
INSERT INTO Table1 (ID, Code, Description) VALUES('ME', '0100', 'diff description')
INSERT INTO Table1 VALUES('ML', '0136', 'description', '7')

SELECT TOP 1 ID, Code, Description, Type
FROM
(
	SELECT ID, Code, Description, Type 
	FROM Table1
	WHERE 
		([ID] = 'ME' AND [Code] = '0100' AND [Type] = '1')
		OR ([ID] = 'ME' AND [Code] = '0100')
) t
ORDER BY Type DESC

DROP TABLE Table1

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-14 : 15:41:06

If I am understanding your needs correctly, here a generic way to implement this type of search:

Requirement:
YOu want to return 1 row that is the best match. The values for column1 and column2 are required to match; if column3 and/or column4 match, even better, but they are not required.

What you can do is pick all rows that match column1 and column2, and then use a CASE statement to check to see if, within that population, there are matches for column3 and/or column4. If so, then we return a value indicating that these rows have a higher "match value", so return them first.

Take a look:


select top 1 *
from YourTable
where Column1 = @Value1 and Column2 = @Value2
order by
  (case when column3= @Value3 then 1 else 0 end) + 
  (case when column4= @Value4 then 1 else 0 end) DESC

That will return only 1 row, and the row must match Column1 and Column2 (thanks to the WHERE clause). However, the expression that we are ORDERing BY says to sort the results so that rows that match Column3 and Column4 first (the expression = 2), followed by rows that match either (=1), followed at last by rows that match neither (=0).

john.youngblood
Starting Member

6 Posts

Posted - 2005-09-14 : 17:09:18

Awesome! Both of your queries work like a charm! Thanks for helping out. I still don't know why my query wouldn't work though. If arranged like this:

if [ (A && D) || (A && B) ]

it should be a very simple logical statement that would short-circuit in some cases. It just never did for me when I ran it in the Query Analyzer. SQL always ran both sides of the OR.

wierd

Thanks for all the help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-14 : 17:56:27

It does short-circuit though per row.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources