• Home |
• Accessibility |
• Log in

• Home
• Projects
• Blog
• Fun

SELECT *
FROM QueryA
LEFT OUTER JOIN QueryB ON QueryA.ID = QueryB.ID

<cfquery name="joinQuery" dbtype="query" >
SELECT *
FROM QueryB
WHERE QueryB.ID = -1
</cfquery>

<cfset QueryAddRow(joinQuery) />

<cfquery name="result" dbtype="query" >
SELECT *
FROM QueryA, QueryB
WHERE QueryA.ID = QueryB.ID

UNION

SELECT QueryA.*, joinQuery.*
FROM QueryA, joinQuery
WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#)
</cfquery>

SELECT *
FROM QueryA
LEFT OUTER JOIN QueryB ON QueryA.ID = QueryB.ID
WHERE QueryB.ID IS NULL

SELECT *
FROM QueryA
WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#)

11 Responses to “How to do an OUTER JOIN in Query of Queries”

1. Pat Says:
   July 17th, 2007 at 11:52 pm

   Doesn’t like
   SELECT *,NULL,NULL

   Query Of Queries syntax error.
   Encountered “NULL. Incorrect Select List, Incorrect select column

   BUT since I need a specific result and know what column types are needed, your code showed the way. THANKS

2. David Says:
   July 19th, 2007 at 9:56 pm

   @ Pat

   Thanks for spotting that.

   I have updated the post to correct the issue. It makes it a bit more complicated, but at least it works

3. richard white Says:
   June 29th, 2008 at 11:32 pm

   hi,

   the code above is not the finished product right? i am trying to replicate it but on the line:

   SELECT QueryA.*, joinQuery

   joinquery is not a collumn therefore it is producing an error.

   Also, you don’t specify a join condition in the second query of your UNION statement, which means that it’ll return a Cartesian product

   thanks

4. David Says:
   August 2nd, 2008 at 3:58 pm

   @Richard

   Yes that’s a typo, it should be SELECT QueryA.*, joinQuery.* I’ve updated the post.

   The CROSS JOIN in the second query is used to include the empty columns from the joinQuery to the filtered results from QueryA. This is done to give the results set of a LEFT OUTER JOIN.

   I should also state that this technique assumes that the joining columns (.ID) can never have a value of -1. e.g. They are auto-numbers of a primary key.

5. Matt Says:
   February 2nd, 2009 at 1:35 pm

   I dont get it.

   I am just going through the logic but dont understand why do this:

   SELECT *
   FROM QueryB
   WHERE QueryB.ID = -1

   When it return zero rows as no ID matches -1

   and then just add 1 row to it.
   Can someone enlighten me or am i missing something here…

6. Thomas Gorgolione Says:
   February 3rd, 2009 at 8:48 pm

   @Matt – With UNION you need the same amount of columns in the second query result as the first, hence the creation of a “null row” in the first few lines and a join with that row to the second query. I was kinda wondering that myself before I looked at UNION here: http://www.w3schools.com/sql/sql_union.asp

7. David Says:
   May 26th, 2009 at 5:43 pm

   Also, as a side note…. if the join field is a string, you’ll need to change the “ValueList” to “QuotedValueList”

   But thanks for this great code. I just ran it on Portable Blue Dragon/Jetty, and it works fine.

8. Garrett Says:
   July 31st, 2009 at 7:10 pm

   I’ve got this set up but the output returns duplicate rows of the list in NOT IN statement rows in addition to the originally selected rows – just without the additional data

   Any thoughts?

   SELECT *
   FROM joinclientquery, GetLossData
   WHERE GetLossData.client_number1 = joinclientquery.cclientid

   UNION

   SELECT joinclientquery.*, joinQuery.*
   FROM joinclientquery, joinQuery
   WHERE joinclientquery.cclientid NOT IN (#quotedValueList(GetLossData.client_number1)#)
   order by cclientname

   sample output

   100386 457,466 0 0
   100386 100386 1 1 0 457,466 0 0

9. Nikos Says:
   July 5th, 2010 at 2:53 pm

   is this still not supported in cf9?

10. Nikos Says:
    July 5th, 2010 at 3:06 pm

    How do you do this if you have 2 join condions?

    cond1 & cont2

    cheers

11. David Beale Says:
    July 8th, 2010 at 11:37 am

    @Nikos

    No its not in CF9

    You should just be able to add extra conditions to the main WHERE expression.

Leave a Reply