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]
