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

Tricky joins / nulls

P: 2
I am having some trouble reaching my intended results in combining information from a few tables. The easiest way to explain is with a dummy model

t1________________________
date - portfolio - group - contn
======================
d1 A a 5
d1 A b 6
d1 A c 7
d2 A b 9
d2 A c 10
d2 A d 11

t2____________
availablegroups
===========
a
b
c
d
e

My objective is to get to either a query or table with
_______________________________
date - portfolio - group - contn
======================
d1 A a 5
d1 A b 6
d1 A c 7
d1 A d 0 OR Null
d1 A e 0 OR Null
d2 A a 0 OR Null
d2 A b 9
d2 A c 10
d2 A d 11
d2 A e 0 OR Null

I've tried using t2 left join t1, but I end up losing the date and portfolio entries for groups that are available for use, but not used in the portfolio.
Bottom line -- I need a contribution number for every group that was available for every date available (the groups available are always the same), regardless of if there are transactions for that group
Any help?

~Chris
Oct 25 '06 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,347
Not at all straightforward (some fun though), but I knocked up a quick test (which seems to work as required).
tblCont:
Date Group Contn
D1 A 5
D1 C 6
D1 E 7
D2 A 10
D2 B 11
D2 C 12

tblGroup:
Group
A
B
C
D
E

Expand|Select|Wrap|Line Numbers
  1. SELECT subAll.Date, subAll.Group, Nz([tblCont].[Contn],[subAll].[Contn]) AS Contn
  2. FROM (SELECT [Date], [Group], 0 AS Contn
  3. FROM tblGroup, (SELECT DISTINCT [Date]
  4. FROM tblCont) AS subDate
  5. ORDER BY [Date], [Group]) AS subAll LEFT JOIN tblCont ON (subAll.Date = tblCont.Date) AND (subAll.Group = tblCont.Group);
Produces :-

Date Group Contn
D1 A 5
D1 B 0
D1 C 6
D1 D 0
D1 E 7
D2 A 10
D2 B 11
D2 C 12
D2 D 0
D2 E 0

NB. Uses two levels of subquery. This is not an accident & I don't think it can be done (to work correctly) with anything less.
Oct 25 '06 #2

Post your reply

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