querying sucessive fields until a hit is made.

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)

querying sucessive fields until a hit is made.

Author

Topic

ranger97
Starting Member

4 Posts

Posted - 2009-05-05 : 02:21:41

i have a table that i want to query first one field and if no results, query next field and so on... until i get a hit and then quit. that result is "blmID AS blmrID"

if there are no hits, not a problem.

i am doing it now by doing sucessive seperate queries whic works but is probably an inefficient way of doing so... can this be written as a single query??? note that i am using the same variable on each query and only changing the field name i.e. blm_ttl, blm_txt, blm_by

please note that this is in coldfusion and cfif statements are like if statements

first query in primary field

<cfquery name="ckstry" datasource="asker">
SELECT MAX(blmID) AS blmrID
FROM blme
WHERE blm_ttl LIKE ('%#blm_adurl1#%')
</cfquery>

check for a hit and if not query second field

<cfif ckstry.blmrID IS "">
<cfquery name="ckstry" datasource="asker">
SELECT MAX(blmID) AS blmrID
FROM blme
WHERE blm_txt LIKE ('%#blm_adurl1#%')
</cfquery>
</cfif>

check for a hit and if not query third field

<cfif ckstry.blmrID IS "">
<cfquery name="ckstry" datasource="asker">
SELECT MAX(blmID) AS blmrID
FROM blme
WHERE blm_by LIKE ('%#blm_adurl1#%')
</cfquery>
</cfif>

this is what i think should be happening but can't get it to work no matter how i phrase it....

<cfquery name="ckstry" datasource="asker">
SELECT MAX(blmID) AS blmrID
FROM blme
WHERE
blm_ttl LIKE ('%#blm_adurl1#%')
if (no variable found in blm_ttl)
begin
blm_txt LIKE ('%#blm_adurl1#%')
end
if (no variable found in blm_txt)
begin
blm_by LIKE ('%#blm_adurl1#%')
end
</cfquery>

Ranger97

BruceT
Yak Posting Veteran

78 Posts

Posted - 2009-05-05 : 11:40:32

Hi,

Take a look at the CASE statement. Something like this or a variation might work.

select MAX(CASE WHEN blm_ttl LIKE ('%#blm_adurl1#%') THEN blmID
WHEN blm_txt LIKE ('%#blm_adurl1#%') THEN blmID
WHEN blm_by LIKE ('%#blm_adurl1#%') THEN blmID
END) as blmrid
FROM blme

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2009-05-05 : 17:18:33

>> I have a table that I want to query first one field [sic: columns are not fields] and if no results, query next field [sic] and so on... until I get a hit and then quit.<<

Since you don't care which column(s) succeed, why not use this?

SELECT MAX(blmID) AS blmr_id
FROM Blme
WHERE blm_ttl LIKE ('%#blm_adurl1#%')
OR blm_txt LIKE ('%#blm_adurl1#%')
OR blm_by LIKE ('%#blm_adurl1#%');

One of the many, many ways that a column is not a field is that columns have no ordering; fields are ordered.

--CELKO--
Joe Celko, SQL Guru

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 01:55:51

I think OP meant the three different columns are prioritized differently.
First search blm_ttl and only if there are no hits, search blm_txt. And if not hit in that column only then search blm_by.

There could be hits for both blm_ttl and blm_txt. For example blm_ttl could have max blmID of 15 whereas max blmID could be 20 for blm_txt. Following the original logix from OP would lead to return 15, since blm_ttl should be searched first and only if no match is found, then search blm_txt and return 20.

SELECT t AS blmr_id
FROM (
SELECT 1 AS prio, MAX(blmID) AS t FROM Blme WHERE blm_ttl LIKE '%#blm_adurl1#%'
UNION ALL SELECT 2, MAX(blmID) FROM Blme WHERE blm_txt LIKE '%#blm_adurl1#%'
UNION ALL SELECT 3, MAX(blmID) FROM Blme WHERE blm_by LIKE '%#blm_adurl1#%'
) AS t
WHERE t IS NOT NULL
ORDER BY prio

E 12°55'05.63"
N 56°04'39.26"

ranger97
Starting Member

4 Posts

Posted - 2009-05-06 : 21:51:18

Peso,

Works, thank you very much. I would be totally stoked if it would stop as soon as it gets a hit, rather than querying the table 2 more times if it gets a hit on query 1, (etc.) as that can put a load on server if there are thousands of long entries.

note that "blm_adurl1" could be: "so long fred" or "the boatmen", etc.... any phrase.

also, could you explain what "t" is... when i try to rename, i get an error... mystic letter?

tnx again

Ranger97

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-06 : 22:26:47

t is the alias for the column defined in the subquery (in red):

SELECT t AS blmr_id
FROM (
SELECT 1 AS prio, MAX(blmID) AS t FROM Blme WHERE blm_ttl LIKE '%#blm_adurl1#%'
UNION ALL SELECT 2, MAX(blmID) FROM Blme WHERE blm_txt LIKE '%#blm_adurl1#%'
UNION ALL SELECT 3, MAX(blmID) FROM Blme WHERE blm_by LIKE '%#blm_adurl1#%'
) AS t
WHERE t IS NOT NULL
ORDER BY prio

He also used t as the alias for the entire subquery, so that might be a little confusing.

quote:
I would be totally stoked if it would stop as soon as it gets a hit, rather than querying the table 2 more times

Unfortunately that's not going to work unless you redesign the table so that this data is stored in a single column. You could however create a stored procedure to search each column in turn and stop at the first one it finds, but it probably won't perform any better than your original code.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-07 : 00:33:45

Rob is right about the confusion. It's a typo from my part.
I normally name my derived table as d.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-07 : 01:54:24

Try this

SELECT TOP 1	blmID AS blmr_id
FROM		blme
WHERE		blm_ttl LIKE '%#blm_adurl1#%'
		OR blm_txt LIKE '%#blm_adurl1#%'
		OR blm_by LIKE '%#blm_adurl1#%'
ORDER BY	CASE
			WHEN blm_ttl LIKE '%#blm_adurl1#%' THEN 1
			WHEN blm_txt LIKE '%#blm_adurl1#%' THEN 2
			WHEN blm_by LIKE '%#blm_adurl1#%' THEN 3
		END,
		blmID DESC

It's not a good solution performance wise, but it fits all your requirements.

E 12°55'05.63"
N 56°04'39.26"

ranger97
Starting Member

4 Posts

Posted - 2009-05-07 : 13:12:38

peso and robvolk...

i really appreciate your input.. am using peso's first posting for now but will update...

the sql i posted above and again here i found in an sql book but couldn't figure out how to make it work... maybe u can make some sense of it. this is sort of what it was saying to do...

<cfquery name="ckstry" datasource="asker">
SELECT MAX(blmID) AS blmrID
FROM blme
WHERE
blm_ttl LIKE ('%#blm_adurl1#%')
if (no variable found in blm_ttl)
begin
blm_txt LIKE ('%#blm_adurl1#%')
end
if (no variable found in blm_txt)
begin
blm_by LIKE ('%#blm_adurl1#%')
end
</cfquery>

tnx again

Ranger97

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-07 : 21:04:14

I don't know anything about ColdFusion, but I don't think that example will give you the results you want. It seems like it would end up running the query up to 3 times to find a match. You're better off using Peter's code in a stored procedure that can accept a parameter for blm_adurl1:

CREATE PROCEDURE FindBLMID @pattern varchar(100) AS
SET NOCOUNT ON
SET @pattern='%' + @pattern + '%'
SELECT TOP 1	blmID AS blmr_id
FROM		blme
WHERE		blm_ttl LIKE @pattern
		OR blm_txt LIKE @pattern
		OR blm_by LIKE @pattern
ORDER BY	CASE
			WHEN blm_ttl LIKE @pattern THEN 1
			WHEN blm_txt LIKE @pattern THEN 2
			WHEN blm_by LIKE @pattern THEN 3
		END,
		blmID DESC

The way to execute it in SQL is: EXEC FindBLMID 'some text'. You'd have to look up the precise syntax for calling a procedure from ColdFusion, but it would probably look like EXEC FindBLMID '#blm_adurl1#'.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2009-05-08 : 12:44:33

>> I think OP meant the three different columns are prioritized differently. First search blm_ttl and only if there are no hits, search blm_txt. And if not hit in that column only then search blm_by. <<

I would agree with you **if** he displayed the source column in a prioritized order. But all he asks for is the MAX(blm_id) in one of the three source columns. Newbies often give specs that assume sequential processing (notice that he confused columns with fields)-- the "how" and not the "what" mindset.

--CELKO--
Joe Celko, SQL Guru

ranger97
Starting Member

4 Posts

Posted - 2009-05-08 : 16:11:55

i really appreciate all of this but the amount of content in the blm_txt "column" will (i hope) be massive and doing a wildcard search in that field will really tax the server which is ok but i would like to avoid it if not necessary i.e. a hit in blm_ttl.

what i guess i am looking for is what the table does when the first WHERE function fails to get a hit and base an if/else on that.
- does it do the first WHERE and then do the OR next.
- if not, can it be made to do that?

the cf script is the same as any script language and acts the same cfquery is query, cfif is if. i have so engrossed myself in this query that i guess i didn't use the right "words"... sorry. this seems like this should have a simple solution but i guess it doesn't.

i am not a newbee as i have been working with msSQL for about 8 yrs. as self taught, i only learn what i need to make my clients sites run - hopefully as efficiently as poss. so i do get stumped sometimes.

tnx again

Ranger97

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources