Query Of Queries : Error When Trying To Fake Left Outer Join

[namtax]

Hi there

I am trying to replicate a left outer join, combining two query of queries using a method I located here

However, I keep getting an error message..

Here is the code I am using....

    <cfquery dbtype="query" name="qry">
                SELECT *
                FROM returnQry, returnQry2
                WHERE returnQry.mediumImage = returnQry2.mediumImage
                ORDER BY returnQry.name   
            </cfquery>
                       
            <cfquery name="returnQry3" dbtype="#application.mx#">
                SELECT *
                FROM trackmeanings AS t
            </cfquery>      

           <cfquery dbtype="query" name="endQry">
                SELECT name,nameRcd,mediumImage, COUNT(sMessage) AS comments
                FROM qry, returnQry3
                WHERE qry.name = returnQry3.sNameTrack
                               
                UNION
               
                SELECT name,nameRcd,mediumImage, COUNT(sMessage) AS comments
                FROM qry, returnQry3
                WHERE #qry.name# NOT IN (#QuotedValueList(returnQry3.sNameTrack)#)
                GROUP BY name,nameRcd,mediumImage
            </cfquery>

When I try to use the query output in a page, i get the error message "Incorrect conditional expression,  Expected one of [like|null|between|in|comparison] condition"

Would anyone have any ideas?

Many thanks

[Dan_Bracuk]

The error is probably coming from the octothorps in the where clause of the lower half of your union query.  There is also a logic problem with that query.  The from clause of bottom part should only have one query, not two.

[namtax]

Hi dan, thanks very much for your response, I have tried altering the union query but still getting errors, wondering how best to write the union query? many thanks

[Dan_Bracuk]

You have the right general idea. The concept is

select 'a' dummy, field1, field2, etc

from query1, query2

where query1.something = query2.something

union

select 'b' dummy, field1, field2, etc

from query1

where query1.something not in (#valuelist(query2.something)#)

[namtax]

Hi, I have tried to adapt you code, as so

<cfquery dbtype="query" name="endQry">

     SELECT name,nameRcd,mediumImage, COUNT(sMessage) AS comments

     FROM qry, returnQry3

     WHERE qry.name = returnQry3.sNameTrack

     UNION

    SELECT name,nameRcd,mediumImage, COUNT(sMessage) AS comments 

    FROM qry

    WHERE qry.name NOT IN (#QuotedValueList(returnQry3.sNameTrack)#)

</cfquery>

But recieving the following error message, and unsure why

Query Of Queries syntax error.
Encountered "qry . name NOT IN ( happy cycling. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition

Happy cycling being the first song outputted by 'returnQry.sNameTrack'

Thanks for your help so far, namtax

[Dan_Bracuk]

If you cfdump that value list, what does it look like?

[namtax]

The sql returned from the query is this

SELECT name,nameRcd,mediumImage,COUNT(sMessage) AS comments

FROM qry, returnQry3

WHERE qry.name = returnQry3.sNameTrack

GROUP BY name,nameRcd,mediumImage

UNION

SELECT name,nameRcd,mediumImage,COUNT(sMessage) AS comments

FROM qry

WHERE qry.name NOT IN (happy cycling,happy cycling,happy cycling,happy cycling,happy cycling,happy cycling,happy cycling,happy cycling,happy cycling,Pigs,Hyph Mngo,Hyph Mngo,Hyph Mngo,Windowlicker,Windowlicker,Windowlicker,Rabbit In Your Headlights,You Do Something To Me,Everything You Do Is a Balloon,Vose In,Further,Lucy in the Sky With Diamonds,Magic America)

GROUP BY name,nameRcd,mediumImage

ORDER BY comments

With the error

Query Of Queries syntax error.
Encountered "qry . name NOT IN ( happy cycling. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition.

Many thanks

[Dan_Bracuk · Accepted solution]

The list items should be quoted.  Since you are using quotedvaluelist, it's odd that they are not.  Nonetheless, if you use cfqueryparam, list=yes, you should have better luck.  Among other things, it will protect you from apostrophe's in any of the names.

[namtax]

Oh, i think i used valueList instead of QuotedValueList in that example, my mistake....

I wasnt aware of cfqueryparam, list=yes, but that has worked perfectly in this instance..

Really want to say thanks for all your help, has helped me immensely

Thanks

[namtax]

Actually , spoke a little too soon, my query now seems to be outputting duplicates when the value is found on both sides of the union, my group by clause doesnt seem to be eliminating them like it usually does.

My SELECT code now reads

<cfquery dbtype="query" name="endQry">

SELECT name,nameRcd,mediumImage, COUNT(sMessage) AS comments

FROM qry, returnQry3

WHERE qry.name = returnQry3.sNameTrack

GROUP BY name,nameRcd,mediumImage

UNION

SELECT name,nameRcd,mediumImage, 0 AS comments

FROM qry

WHERE qry.name NOT IN (<cfqueryparam

value="#returnQry3.sNameTrack#"

cfsqltype="cf_sql_varchar"

list="yes"

/>)

GROUP BY name,nameRcd,mediumImage

ORDER BY name DESC

</cfquery>

and my ouput...is producing duplicates

<cfoutput query="rc.qryTopTracks" group="name">

#rc.qryTopTracks.name#

</cfoutput>

Would you have any idea of how to eliminate them here?  Thanks

[Dan_Bracuk]

Regarding:

and my ouput...is producing duplicates

Would you have any idea of how to eliminate them here?  Thanks

It depends on what you want.  Reading the entire thread, you might want to do a Q of Q of that last query, and include sum(something).

[namtax]

Hi there dan..

I have managed to remove duplicates by changing the code

WHERE qry.name NOT IN (<cfqueryparam

value="#returnQry3.sNameTrack#"

cfsqltype="cf_sql_varchar"

list="true"

separator=","

/>)

to

WHERE qry.name NOT IN (<cfqueryparam

value="#ValueList(returnQry3.sNameTrack)#"

cfsqltype="cf_sql_varchar"

list="true"

separator=","

/>)

Thanks