>Not necessarily. How many records are in joinQuery? It should just have one,
>empty record. The point of the first query is to get the columns, but not to
>select any records; the empty record is created directly below it.
oh yes your right joinquery shouldnt have any records
basically i am trying to run this to outer join 2 queries using 3 primary key
fields - subjectID, projectID, and testOccasionID so i have adapted it as
follows:
<cfquery name="joinQuery" dbtype="query">
SELECT *
FROM QueryB
WHERE QueryB.subjectID = -1
</cfquery>
<cfset QueryAddRow(joinQuery) />
<cfquery name="result" dbtype="query">
SELECT *
FROM QueryA, QueryB
WHERE QueryA.subjectID = QueryB.subjectID
and QueryA.projectID = QueryB.projectID
and QueryA.testOccasionID = QueryB.testOccasionID
UNION
SELECT QueryA.*, joinQuery.*
FROM QueryA, joinQuery
WHERE QueryA.subjectID NOT IN (#ValueList(QueryB.subjectID)#)
and QueryA.projectID NOT IN (#ValueList(QueryB.projectID)#)
and QueryA.testOccasionID NOT IN (#ValueList(QueryB.testOccasionID)#)
</cfquery>
this doesnt create more rows in fact it actually takes rows off and produces
duplicate columns, and doesnt work as a normal sql outer join would
instead of using the joinquery.* i will get the column list, take out the
primary keys fields so it doesnt duplicate them and then see whats happening
thanks dave
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308355
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
