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
ok im getting there and have narrowed it down to 1 more problem - i hope!!
the problem is coming from the second part of the union where i am trying to
find the rows that are eliminated in the first inner join part
this problem only stems from the fact that i have more than 1 primary key
if i
This is off the top of head, but what if you did:
where not (queryA.ID1 in #valuelist(queryB.id1)#
and queryA.ID2 in #valuelist(queryB.id2)#
and queryA.ID3 in #valuelist(queryB.id3)#)
That way the valuelists are done together as a group, and then the not
applies to all of them combined.
Does
Hi Brad, i thought that would work but not producing the result as expected, it
still returns no rows on 2 queries where i know queryb has 15 rows that are not
in querya therefore it should be returning those 15 but is not
thanks
This is off the top of head, but what if you did:
where not
in
(#valuelist(queryA.id3)#))
/cfquery
cfdump var=#qry_left_outer#
~Brad
-Original Message-
From: Richard White [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2008 2:13 PM
To: CF-Talk
Subject: Re: left outer join on query of query function
Hi Brad, i thought that would work
thanks for the description brad, yes sorry i wrote it wrong they do exists in
query a and not in query b so i want to find those that exist in querya and not
in query b.
however your example is quite detailed and shows that the 'where not ()' clause
should do the trick so there must be
confirming that
what you think exists in them actually does.
~Brad
-Original Message-
From: Richard White [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2008 3:14 PM
To: CF-Talk
Subject: Re: left outer join on query of query function
thanks for the description brad, yes sorry i wrote
thanks brad no i hadn't omitted them for simplicity, i will try that as well
then
thanks again you have been really helpful, will let you know what happens
Ok, good luck. Note: I had to fiddle a little bit for my example to
work. For instance, there needed to be () around the output of the
actually just looked at my real example and i do have the brackets!!! i think
tiredness is getting me now, ill stop till tomorrow and try again, i find
sometimes just taking my mind away from it makes it easier when i come back
thanks again
richard
thanks brad no i hadn't omitted them for
i see, thanks for your help dave, i am actually trying to do this on more than
1 primary key and it is not producing any errors when i add the joinquery.* but
it is producing 2 times more rows than i expect, so it must be me missing a
where clause somewhere.
seeing as you also indicate this
i see, thanks for your help dave, i am actually trying to do
this on more than 1 primary key and it is not producing any
errors when i add the joinquery.* but it is producing 2 times
more rows than i expect, so it must be me missing a where
clause somewhere.
Not necessarily. How many
i have been following the following link that discusses how
to do a left outer join on a query of query:
http://www.bealearts.co.uk/blog/2007/06/20/how-to-do-an-outer-
join-in-query-of-queries/
this is the code that is meant to do it:
cfquery name=ââ¬ÂjoinQueryââ¬Â
thanks Dave,
i basically have just copied and pasted the code from the link i pasted above:
do you have any idea how to rectify this or even if this would work.
the guy indicates that his code is the finished product but now that
credibility is lost, so just wondering whether you think it is
i basically have just copied and pasted the code from the
link i pasted above: do you have any idea how to rectify this
or even if this would work.
I hadn't read the link, just looked at your code. Not having read the code,
I didn't realize that joinQuery would contain a single record with
14 matches
Mail list logo