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]
