Query Weirdness

[Sarky78]

I have got a simple query that I have used for a while, to get the newest record from a table. I have added a where clause to the query so that I only get the newest record for the category that i want. The problem that I am having is that the query is returning a record when one does not exist. I think that the where clause is being ignored in the first query (getMaxQuestion), and returning incorrect information.

The query looks like this:

<cfquery datasource=&quot;test&quot; ame=&quot;getMaxQuestion&quot;>
SELECT Max(CompetitionID) as maxComp
FROM tblCompetition
WHERE Hotel = #variables.hotel#
</cfquery>

<cfquery datasource=&quot;test&quot; name=&quot;getQuestion&quot;>
SELECT *
FROM tblCompetition
WHERE CompetitionID = #getMaxQuestion.maxComp#
</cfquery>

Is this the case, can anyone think of a way of getting around this problem ??

 

[iza]

-> this is a sql question and as such it would be better in the SQL forum
-> which is the query returning an answer even where there's no answer ?
-> add the maxrow=1 in the cfquery tag of the 1st query, as you expect only one row !

 

[CFHub]

Getting the &quot;new&quot; ID from a table is pretty common. You may like to take a look at how nicely CFTRANSACTION and @@identity work with each other (MS SQL server/Access only)

http://cfhub.com/advanced/cftransaction/

 

[Sarky78]

CFHub I am not getting the newest ID from the DB. I am using the same table for three sister sites for sections that are the same. There will be three administrators that can only add to their site. These use three seperate Hotel ID's, that allow me to identify the competition for that specific hotel. The newest competition may not be the competition for that hotel, so using the that method is not going to work.

What I have tried is:

SELECT MAX(CompetitionID) as max
FROM tblComps
WHERE Hotel = #variables.hotelID#

This is returning information for the wrong hotel. The #variables.hotelID# is defined in each of the sites app.cfm's. what it appears to be doing is ignoring the where statement, and just returning the newest(max) entry.

Any ideas why the where clause would b ignored ?

 

[iza]

yes ! USE CFOUTPUT !!!!!!!!!!!! else you're looking for the &quot;#variable.hotelID#&quot; string NOT its value !!!!!!

 

[Sarky78]

ITS IN A CFQUERY YOU DON'T NEED A CFOUTPUT !!!!!

This is the debug code from the page, showing the query with the variable that has been picked up from the app.cfm !!!!

BEFORE COMPILATION

SELECT MAX(CompetitionID) as max
FROM tblComps
WHERE Hotel = #variables.hotelID#

AFTER COMPILATION

SELECT Max(CompetitionID) as maxComp
FROM tblCompetition
WHERE Hotel = 2

 

[iza]

oh ! shame on me it's been to long i haven't coded a cfm app !!!
and you have an hotel which id is 2 in the db, and you don't get any value ?????? go to sql forum then, i'm sure people there are better than us coldfusion coders to find out what you're mising then !!

 

[Sarky78]

Been over there and at the moment no response. There is no entry in the database for hotel 2, but that is a possibility so has to be tested for. At the moment it is retuirning a record, when there is nothing in the database for that hotel ! (weird) and when i do an output on that there is nothing in the value to show so it is just drving me up the wall!!!

Will see if i can bother the SQL lot then for a while !!

Cheers

 

[iza]

ok i've got a tricky idea, but it's not elegant at all and i guess a pure sql guy would get totally mad reading this (it's just because i don't know how max works ...) and i'm not even sure it'll be working better !!!

SELECT nvl(MAX(CompetitionID), &quot;no_value&quot as max
FROM tblComps
WHERE Hotel = #variables.hotelID#

and then just add
<cfif #getMaxQuestion.max# neq &quot;no_value&quot;>
<cfquery datasource=&quot;test&quot; name=&quot;getQuestion&quot;>
....
<cfelse>
i'm sorry this hotel doesn't even exists !!
</cfif>

let me know ....
hey, are those real hotels ? could we win a night in one of those ;]]]] !???

 

[Sarky78]

thinking along the same lines here. I have just done this:

<CFQUERY NAME=&quot;getMaxQuestion&quot; DATASOURCE=&quot;test&quot;>
SELECT Max(CompetitionID) as maxComp
FROM tblCompetition
WHERE Hotel = #variables.hotel#
</CFQUERY>
<cfif #Trim(getMaxQuestion.maxComp)# NEQ &quot;&quot;>
<cfquery datasource=&quot;test&quot; name=&quot;getQuestion&quot;>
SELECT *
FROM tblCompetition
WHERE CompetitionID = #getMaxQuestion.maxComp#
</cfquery>
</CFIF>

and that seems to work fine, when there is a record and when there isn't a record !

 

[iza]

cool then ! i'm happy for you