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]
