My brain hurts.

Table 1

PublicityCampaign (AutoInc)
PublicityCampaignName (String)

Table 2

PublicityCampaign (Int)
PressReporter (Int)
DatePressReleaseUsed (Date)

Table 3

PressReporter (AutoInc)
ReporterName (String)

What I want is:

PublicityCampaignName
DatePressReleaseUsed
ReporterName

I am getting X (squared) records returned instead of X, if X = Table2 record count

I can denormalize a bit, but I used to know how to do this in SQL. Anyway, my brain hurts.

Groan

Herb

Herb (Kraft) <herb@examprep.us> wrote on Tue, 8 Aug 2006 17:02:05 -0400

>My brain hurts.
>
>Table 1
>
>PublicityCampaign (AutoInc)
>PublicityCampaignName (String)
>
>Table 2
>
>PublicityCampaign (Int)
>PressReporter (Int)
>DatePressReleaseUsed (Date)
>
>Table 3
>
>PressReporter (AutoInc)
>ReporterName (String)
>
>What I want is:
>
>PublicityCampaignName
>DatePressReleaseUsed
>ReporterName
>
>I am getting X (squared) records returned instead of X, if X = Table2 record count
>
>I can denormalize a bit, but I used to know how to do this in SQL. Anyway, my brain hurts.
>
>Groan
>
>Herb
>
Herb

How's this?


SELECT T1.PublicityCampaignName,
            T2.DatePressReleaseUsed,
            T3.ReporterName
From Table2 T2
LEFT OUTER JOIN Table1 T1 ON (T1.PublicityCampaign = T2.PublicityCampaign)
LEFT OUTER JOIN Table1 T3 ON (T3.PressReporter = T2.PressReporter)
WHERE ...

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:C2DE130E-7500-46F9-9AA9-DD59BE35C19C@news.elevatesoft.com...
>
>
> SELECT T1.PublicityCampaignName,
> T2.DatePressReleaseUsed,
> T3.ReporterName
> From Table2 T2
> LEFT OUTER JOIN Table1 T1 ON (T1.PublicityCampaign = T2.PublicityCampaign)
> LEFT OUTER JOIN Table1 T3 ON (T3.PressReporter = T2.PressReporter)
> WHERE ...
>

Why LEFT OUTER JOINS, instead of simply JOIN? With LO you would pick up any
record in table 2 that does not have a corresponding record in tables 1
and/or 3. Then again, maybe it does not matter for this application, these
politicians like to inflate numbers anyway.

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote on Tue, 8 Aug 2006 17:52:09 -0400

>
>"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
>news:C2DE130E-7500-46F9-9AA9-DD59BE35C19C@news.elevatesoft.com...
>>
>>
>> SELECT T1.PublicityCampaignName,
>> T2.DatePressReleaseUsed,
>> T3.ReporterName
>> From Table2 T2
>> LEFT OUTER JOIN Table1 T1 ON (T1.PublicityCampaign = T2.PublicityCampaign)
>> LEFT OUTER JOIN Table1 T3 ON (T3.PressReporter = T2.PressReporter)
>> WHERE ...
>>
>
>Why LEFT OUTER JOINS, instead of simply JOIN? With LO you would pick up any
>record in table 2 that does not have a corresponding record in tables 1
>and/or 3. Then again, maybe it does not matter for this application, these
>politicians like to inflate numbers anyway.
>
>Robert
>
>
Robert


I assumed that Table2 was data that was to be reported on - the JOIN's just being look up's to translate codes to real data.  In that case you don't want to drop stuff because of a lack of Referential Integrity (which can not be assumed in DBISAM up to v4) so I used LEFT OUTER.

Of course, if I really believed that, I would have written:-

SELECT IF(NOT T1.PublicityCampaignName IS NULL THEN T1.PublicityCampaignName  
                ELSE 'Unknown Campaign ' + CAST(T2.PublicityCampaign AS CHAR(8)),
..
..
..
Cheers

Jeff Cook
--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Herb,

<< I am getting X (squared) records returned instead of X, if X = Table2
record count >>

Just throw a DISTINCT clause in there.

--
Tim Young
Elevate Software
www.elevatesoft.com