Re: conditional join?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


"Bart" <Bart@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8DCDDF8B-1F08-477A-8782-0A9FAD40663C@xxxxxxxxxxxxxxxx
Hi Chris,

I believe I did try what you suggest below and indeed got the result that
you suggest below which is indiscriminate in regard to whether a match was
possible at a deeper level. I am assuming I need to put in some conditional
coding but I don't know where I can do that.

Paul



Paul,

I am unable to determine what it is you wish to do.

Are you trying to distinguish between what records were found with 4-column matching,
3-column matching, 2-column matching, and 1-column matching?

If so, you could run a UNION with four SELECT statements, with an additional column to
distinguish the groups, like so:

Query:

SELECT 1 As MatchingGroups
,T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON (T1.Index1 = T2.Index1
AND T1.Index2 = T2.Index2
AND T1.Index3 = T2.Index3
AND T1.Index4 = T2.Index4)
UNION ALL
SELECT 2 As MatchingGroups
,T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON (T1.Index1 = T2.Index1
AND T1.Index2 = T2.Index2
AND T1.Index3 = T2.Index3)
UNION ALL
SELECT 3 As MatchingGroups
,T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON (T1.Index1 = T2.Index1
AND T1.Index2 = T2.Index2)
UNION ALL
SELECT 4 As MatchingGroups
,T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON (T1.Index1 = T2.Index1)


Comments:

I tried entering some test data into the tables I created, and discovered something that
makes me feel certain I did not guess structure correctly.

With the way the rules are set up, all four columns appear to have to have the same
values, rendering columns 2, 3, and 4 irrelevant (which can't be right, I think).

Can you please post your table structures (including all indexes and relationships),
sample data, desired results, and any SQL attempted to date (including why it didn't
work).


Sincerely,

Chris O.


.

Relevant Pages

  • Re: Query miscalculations, why?
    ... record as there are matching records in the other table. ... if you have tableMain joined to TableA and TableB; ...
    (microsoft.public.access.queries)
  • Re: Need Help ASAP .. PLEASE
    ... > the date and find the matching date in column U. ... I think I can help you, but a sheet with the data would be helpful. ... You can send your test data to my email address. ... Striker ...
    (microsoft.public.excel.worksheet.functions)