[ cf-dev ] SQL Headache

[Dave Phipps]

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]