I'm getting it directly from the tag. The main problem is that joins are not allowed in QoQ. Union is and I tried this: select name, [Size] as size1, '' as size2 from cfdirectory1 union select name, [Size] as size2, '' as size1 from cfdirectory2 But to no effect. If I could get all of the names from query 1 and all the names from query 2 and have them side by side based on the names, I can deal with it. I've used this select cfdirectory2.name, cfdirectory2.[Size] as size2, cfdirectory1.[Size] as size1 from cfdirectory1, cfdirectory2 where cfdirectory1.name=cfdirectory2.name to get me all of the items that do match, but I don't have the same thing to get me the items that don't match. In doing this I'm learning the limitations of QoQ (like Size being reserver and having to be escaped).
> Since you are manipulating cfdirectory data, I imagine you want to use > QOQ? Or is the data going into a database first? > > You could union the two and then do a self-join. Do something > (conceptually) along these lines: > > <cfdirectory1> > <cfdirectory2> > > <cfquery1> > select * > from cfdirectory1 > UNION > select * > from cfdirectory2 > </cfquery1> > > > <cfquery2> > select * > from cfquery1 as cfq1 inner join cfquery1 as cfq2 on > (cfq1.name = cfq2.name and cf1.size != cfq2.size) > </cfquery2> > > Your resulting query should have all the non-equal elements, which (I > understand) is not quite the same as having a query with all the elements > and 'differences flagged.' > > You might also try something like this (a completely different approach): > > <cfdirectory1> > <cfdirectory2> > > <cfquery> > select * from cfdirectory1, cfdirectory2 > where cfdirectory1.name = cfdirectory2.name and > not (cfdirectory1.size in (select cfdirectory.size from > cfdirectory2 where cfdirectory2.name = cfdirectory1.name)) > </cfquery> > > > Anyway, just two ideas... No guarantees that either one will work. > > > At 04:13 PM 7/3/2002 -0400, you wrote: > >Let say I have 2 queries result sets from 2 different sources and I want > >to compare them. i.e. if there's a record different between them than that > >record will be flagged in some way. Records that are the same are not > >flagged. Is there an easy way to do this in a SQL statement? > >The goal is to run a CFDIRECTORY on 2 different directories and show what > >files are in one and not the other or what files in one are different in > >size than the other. I'm sure I could write a sort mechanism in CF, but I > >think that a SQL way would be more efficient if it exists. > >Thanks > > > >Michael Dinowitz > >Master of the House of Fusion > >http://www.houseoffusion.com > > > > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
