use the Query functions, QueryNew, QuerySetCell etc, to construct a new query based on your two queries.
Duncan Cumming IT Manager http://www.alienationdesign.co.uk mailto:[EMAIL PROTECTED] Tel: 0141 575 9700 Fax: 0141 575 9600 Creative solutions in a technical world ---------------------------------------------------------------------- Get your domain names online from: http://www.alienationdomains.co.uk Reseller options available! ---------------------------------------------------------------------- ---------------------------------------------------------------------- Dave Phipps <dave@phipps- To: <[EMAIL PROTECTED]> cf.co.uk> cc: Subject: RE: [ cf-dev ] SQL Headache 01/29/03 03:52 PM Please respond to dev Hi is there anyway to combine these two queries without using union? We are on a version of MySQL that does not support UNION. <cfquery name="releaseTest" datasource="#request.dsn#" dbtype="ODBC"> SELECT storySub.storyid, journoDetails.journoid, towns.town, journoDetails.journoName, region.regionid FROM storySub, towns LEFT JOIN journoTown ON towns.townid = journoTown.townid LEFT JOIN journoDetails ON journoTown.journoid = journoDetails.journoid LEFT JOIN storyTown ON towns.townid = storyTown.townid LEFT JOIN region ON towns.regionid = region.regionid WHERE storySub.storyid = storyTown.storyid </cfquery> <cfquery name="releaseTestRegion" datasource="#request.dsn#" dbtype="ODBC"> SELECT journoDetails.journoid, journoDetails.journoName FROM journoDetails, journoRegion WHERE journoDetails.journoid = journoRegion.journoid AND journoRegion.regionid = '#releaseTest.regionid#' </cfquery> Cheers Dave At 15:16 1/29/2003 +0000, you wrote: >either that or union the two outputs together ? > >ALex > >-----Original Message----- >From: Alex Skinner [mailto:[EMAIL PROTECTED]] >Sent: 29 January 2003 15:15 >To: [EMAIL PROTECTED] >Subject: RE: [ cf-dev ] SQL Headache > > >2 seperate queries used in an in statement ? > > > >SELECT DISTINCT STORY ID,otherfields FROM allstories WHERE storyid IN >(select query for story joined against towns) >OR storyid IN (select query joined against region) > >Yes I know its not all sql but you get the idea > >Alex > > >-----Original Message----- >From: Dave Phipps [mailto:[EMAIL PROTECTED]] >Sent: 29 January 2003 14:42 >To: [EMAIL PROTECTED] >Subject: Re: [ cf-dev ] SQL Headache > > >The problem I am having is that if I do it as a separate query then the >wrong story may get sent to the wrong journalist. > >I am having trouble seeing the wood from the trees today. I'll try to >break it up into something simpler. > >Cheers > >Dave > >At 14:02 1/29/2003 +0000, you wrote: > > >can you just do a second query for the additional info you need? > > > > > >Duncan Cumming > >IT Manager > > > >http://www.alienationdesign.co.uk > >mailto:[EMAIL PROTECTED] > >Tel: 0141 575 9700 > >Fax: 0141 575 9600 > > > >Creative solutions in a technical world > > > >---------------------------------------------------------------------- > >Get your domain names online from: > >http://www.alienationdomains.co.uk > >Reseller options available! > >---------------------------------------------------------------------- > >---------------------------------------------------------------------- > > > > > > > > > > > > > > Dave > > Phipps > > > > <dave@phipps- To: ColdFusion User group > > <[EMAIL PROTECTED]> > > cf.co.uk> cc: > > > > Subject: [ cf-dev ] SQL > > Headache > > 01/29/03 > > > > 12:31 > > PM > > > > Please > > > > respond > > to > > > > dev > > > > > > > > > > > > > > > > > > > > > >Hi, > > > >I am having a complete '4 hour sleep' foggy brain day today and I need to > >get a query written and the fog is not helping: > > > >I have the following situation: Journalists are linked to either towns > >and/or regions and national options. Any one journo can be assigned to a > >region and several towns not necessarily within the region but they cannot > >also have national options. I also have stories which are linked to > >regions and towns in the same way. > > > >What I want to do is a query that grabs the story finds the related townid > >and/or regionid and then links the story to the journalist. > > > >I have the following tables: > > > >storySub (holds the story) > >storyRegion (links story to region) > >storyTown (links town to story) > >storyNatOpt (links story to national options) > >journoDetails (holds journo) > >journoRegion (links region to journo) > >journoTown (links town to journo) > >journoNatOpt (links national opt to journo) > >towns (list of towns and linked to region) > >region (list of regions) > >natOption (list of national options) > > > >For example: A story is linked with the towns Oxford and Chipping Norton > >which belong to the Oxfordshire region. There are two journalists that are > > > >linked to one or more of these towns. There is also a journalist that is > >linked to Oxfordshire. This journo should also receive the story that is > >only linked to Oxford and Chipping Norton. > > > >I can get the journos linked with the towns which outputs the following: > > > > JOURNOID JOURNONAME REGIONID STORYID > >TOWN > > 86 Christine > >Smith 50 30 Chipping Norton > > CA Dave > >Phipps 50 30 Oxford > > CA Dave > >Phipps 50 56 Oxford > > > >What I also want to appear with this query is an extra two records which > >correspond the Oxfordshire(regionid 50) journo : > > > > JOURNOID JOURNONAME REGIONID STORYID > >TOWN > > 18 Dave > >2 50 30 Chipping Norton > > 18 Dave > >2 50 30 Oxford > > > >Can any one think of a way around this. One limitation is I can only use > >LEFT or RIGHT JOINS as I am using MySQL. > > > >The query that outputs the first lot of data is: > > > ><cfquery name="releaseTest" datasource="#request.dsn#" dbtype="ODBC"> > >SELECT storySub.storyid, journoDetails.journoid, towns.town, > >journoDetails.journoName, region.regionid > >FROM storySub, towns LEFT JOIN journoTown ON towns.townid = > >journoTown.townid > > LEFT JOIN journoDetails ON journoTown.journoid > >= > >journoDetails.journoid > > LEFT JOIN storyTown ON towns.townid = > >storyTown.townid > > LEFT JOIN region ON towns.regionid = > >region.regionid > > LEFT JOIN journoRegion ON region.regionid = > >journoRegion.regionid > > > >WHERE storySub.storyid = storyTown.storyid > ></cfquery> > > > >Any help to clear the fog would be really appreciated. > > > >Cheers > > > >Dave > > > > > >-- > >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > >To unsubscribe, e-mail: [EMAIL PROTECTED] > >For additional commands, e-mail: [EMAIL PROTECTED] > >For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > >-- > >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > >To unsubscribe, e-mail: [EMAIL PROTECTED] > >For additional commands, e-mail: [EMAIL PROTECTED] > >For human help, e-mail: [EMAIL PROTECTED] > > >============================================ >Phipps CF Development >Oxford, Oxfordshire. >Telephone: +44(0)7718 896696 >http://www.phipps-cf.co.uk >============================================ > > >-- >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >For human help, e-mail: [EMAIL PROTECTED] > > > > >-- >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >For human help, e-mail: [EMAIL PROTECTED] > > > > >-- >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >For human help, e-mail: [EMAIL PROTECTED] ============================================ Phipps CF Development Oxford, Oxfordshire. Telephone: +44(0)7718 896696 http://www.phipps-cf.co.uk ============================================ -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
