By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,537 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

RIGHT JOIN Doesn't Show all Parent Records

P: 1
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, 189 views)
Nov 24 '11 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,186
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.