469,091 Members | 1,219 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

RIGHT JOIN Doesn't Show all Parent Records

Dear expert,

I'm trying to join two table between master and child table. I want to show all aircraft registration for specified aircraft type even if there is no flight record at the time specified.

Expand|Select|Wrap|Line Numbers
  1. SELECT TYPE,
  2. SREG,
  3. Sum(FLT_MINUTES)/60/7 AS [AVG FH],
  4. Sum(FLT_MINUTES) AS FM,
  5. Count(FLT_MINUTES) AS CYCLE
  6. FROM AC RIGHT JOIN WEEKLY ON AC.SREG = WEEKLY.AC_REG
  7. WHERE DEP_UTC BETWEEN #10/31/2011# AND #11/6/2011# AND TYPE = "A330-200"
  8. GROUP BY TYPE, SREG;
  9.  
Unfortunately, from my query result, I cannot see aircraft registration "GPN" (which is an A330-200 and exist but not flight at the time specified). please see attachment.

Please help.

Attached Images
File Type: jpg query result.jpg (46.3 KB, 244 views)
Nov 24 '11 #1
1 1407
NeoPa
32,159 Expert Mod 16PB
Harapan:
I want to show all aircraft registration for specified aircraft type even if there is no flight record at the time specified.
That begs the question :
Why include that in the WHERE clause then?

It's hard to imagine you wouldn't realise that specifying a range in the WHERE clause excludes all records which don't conform to that specification.

If you want master records with no child records, then you have to handle the possibility of Nulls in [DEP_UTC] (which I'm having to guess is from [AC]), but be careful as this will still exclude master records if there are child records, but none is in the specified period.
Nov 24 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Quarco | last post: by
1 post views Thread by Paul Bramscher | last post: by
2 posts views Thread by Ryan | last post: by
1 post views Thread by rossz | last post: by
9 posts views Thread by Alan Lane | last post: by
2 posts views Thread by New Guy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.