Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Three Tables |
Tue, Aug 8 2006 5:02 PM | Permanent Link |
Herb (Kraft) | 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 |
Tue, Aug 8 2006 5:19 PM | Permanent Link |
Jeff Cook | 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 |
Tue, Aug 8 2006 5:55 PM | Permanent Link |
"Robert" | "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 |
Tue, Aug 8 2006 6:45 PM | Permanent Link |
Jeff Cook | "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 |
Wed, Aug 9 2006 2:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |