INNER JOIN argument doubled?

[Christian Muenscher]

Dear group!

The following statement gives me a resultset containing two columns
containing a PersonID:

SELECT *
FROM A
LEFT JOIN B ON A.PersonID=B.Pe rsonID
UNION SELECT *
FROM A
RIGHT JOIN B ON A.PersonID=B.Pe rsonID;

How can I change this statement to get only one PersonID that covers
the PersonIDs from A and B? I need this combined PersonID because I
have to combine that union with some more tables like this...

SELECT *
FROM D
LEFT JOIN Union_01 ON D.PersonID=Unio n_01.PersonID
UNION SELECT *
FROM D
RIGHT JOIN Union_01 ON D.PersonID=Unio n_01.PersonID;

And that only works with one PersonID, otherwise Access complains -
rightly!

Originally I wanted to combine five tables, also covering all
PersonIDs that only appear in some, but not in all tables, but I did
not find a way using Access (2003) - anyone got an idea?

SELECT A.*, B.*, D.*, R.*, T.*
FROM (((A INNER JOIN B ON A.Personen_ID=B .Personen_ID) INNER JOIN D ON
B.Personen_ID=D .Personen_ID) INNER JOIN R ON
D.Personen_ID=R .Personen_ID) INNER JOIN T ON
R.Personen_ID=T .Personen_ID
ORDER BY A.Personen_ID;

This is nice, but skips the mentioned entries where a Person_ID e.g.
only appears in table D :-(

Thank you very much!

With kind regrads,
Chriss

P.S: Originally, I had five Excel-Tables I wanted to combine... *sigh*

[Smartin]

Re: INNER JOIN argument doubled?

Christian Muenscher wrote:

Dear group!
>
The following statement gives me a resultset containing two columns
containing a PersonID:
>
SELECT *
FROM A
LEFT JOIN B ON A.PersonID=B.Pe rsonID
UNION SELECT *
FROM A
RIGHT JOIN B ON A.PersonID=B.Pe rsonID;

[snipped]

Hi Chriss,

If you only want to see PersonID from one table try

SELECT A.*
FROM A
LEFT JOIN B ON A.PersonID=B.Pe rsonID
UNION SELECT A.*
FROM A
RIGHT JOIN B ON A.PersonID=B.Pe rsonID;

or, specify exactly which fields you want to see from A and/or B.

Hope this helps
--
Smartin

[Christian Muenscher]

Re: INNER JOIN argument doubled?

Hi Smartin!

On 21 Feb., 01:45, Smartin <smartin...@yah oo.comwrote:

If you only want to see PersonID from one table try
>
SELECT A.*
FROM A
LEFT JOIN B ON A.PersonID=B.Pe rsonID
UNION SELECT A.*
FROM A
RIGHT JOIN B ON A.PersonID=B.Pe rsonID;
>
or, specify exactly which fields you want to see from A and/or B.

Maybe I was not exact enough... I don't want to see only the PersonID
from one table, I want to see *all* PersonIDs, that means I want to
see the PersonIDs from table one plus the eventually additional
PersonIDs from table two plus the eventually additional PersonIDs from
table three plus... and so on. And I want to have only that one column
with PersonIDs for all tables. Difficult :(

Thanks for your help!

With kind regards,
Chriss

[Sunil Korah]

Re: INNER JOIN argument doubled?

On Feb 21, 2:30 pm, "Christian Muenscher" <ccr...@gmail.c omwrote:

Hi Smartin!
>
On 21 Feb., 01:45, Smartin <smartin...@yah oo.comwrote:
>

If you only want to see PersonID from one table try

>

SELECT A.*
FROM A
LEFT JOIN B ON A.PersonID=B.Pe rsonID
UNION SELECT A.*
FROM A
RIGHT JOIN B ON A.PersonID=B.Pe rsonID;

>

or, specify exactly which fields you want to see from A and/or B.

>
Maybe I was not exact enough... I don't want to see only the PersonID
from one table, I want to see *all* PersonIDs, that means I want to
see the PersonIDs from table one plus the eventually additional
PersonIDs from table two plus the eventually additional PersonIDs from
table three plus... and so on. And I want to have only that one column
with PersonIDs for all tables. Difficult :(
>
Thanks for your help!
>
With kind regards,
Chriss

If you want something that just works, I would do it this way. (I am
no expert, and may be that's why this occurs to me)

1. SELECT A.PersonID INTO tmpID FROM A; 'Creates a table tmpID

2. INSERT INTO tmpID ( PersonID ) 'Appends into tmpID
SELECT B.PersonID
FROM B;

do this for all the tables you want and then finally

SELECT DISTINCT tmpID.PersonID FROM tmpID; 'Get all the
unique IDs

After this you can delete the table tmpID.

There might be more efficient ways to do this. But this works.

HTH

Sunil Korah

[Christian Muenscher]

Re: INNER JOIN argument doubled?

Dear Sunil!

On 21 Feb., 14:50, "Sunil Korah" <kor...@gmail.c omwrote:

There might be more efficient ways to do this. But this works.

Thanks, that works fine! But now I ran into a different problem... I
connected all seven table's PersonID to the PersonID of the temporary
table via an INNER JOIN. That selects all I want as long as the
PersonIDs are the same in all tables. But if I've got PersonID 1 to 5
in table 1, ID 6-10 in table 2, 11-15 in table 3 and so on (all
different) then I only get an empty result set. What will I have to do
here? Use a LEFT/RIGHT JOIN?

With kind regards,
Chriss

[Sunil Korah]

Re: INNER JOIN argument doubled?

On Feb 22, 1:28 pm, "Christian Muenscher" <ccr...@gmail.c omwrote:

Dear Sunil!
>
On 21 Feb., 14:50, "Sunil Korah" <kor...@gmail.c omwrote:
>

There might be more efficient ways to do this. But this works.

>
Thanks, that works fine! But now I ran into a different problem... I
connected all seven table's PersonID to the PersonID of the temporary
table via an INNER JOIN. That selects all I want as long as the
PersonIDs are the same in all tables. But if I've got PersonID 1 to 5
in table 1, ID 6-10 in table 2, 11-15 in table 3 and so on (all
different) then I only get an empty result set. What will I have to do
here? Use a LEFT/RIGHT JOIN?
>
With kind regards,
Chriss

First of all, I was away in between and saw the post only now.

More importantly, I am not clear about what you mean. Which is 'the
temporary table' that you mention. I cant see any point in connecting
fields which do not have common values. It would be better if you can
explain what you are trying to achieve. What information are you
trying to get out of your tables?

Sunil Korah

[Christian Muenscher]

Re: INNER JOIN argument doubled?

Dear Sunil!

On 27 Feb., 18:03, "Sunil Korah" <kor...@gmail.c omwrote:

More importantly, I am not clear about what you mean. Which is 'the
temporary table' that you mention. I cant see any point in connecting
fields which do not have common values. It would be better if you can
explain what you are trying to achieve. What information are you
trying to get out of your tables?

The temporary table I mentioned is the table tmpID you introduced some
posts earlier. I will try to explain my problem in detail:
Our students sometimes are having some kind of practical exams in
different sub-subjects. For each of these sub-subjects, I get one
table containing the results of that exam, this time there are seven
tables. The data is being created by scanning the rater forms using a
high-speed duplex document scanner, the Software "TeleForm" outputs
the results after a manual correction cycle. It is possible that some
students only take part in e.g. three of these exams. Now the
lecturers want me to deliver *one* table (best in Excel) containing
the accumulated results of all (seven in this case) exams. Here a
simplyfied example:

table 1:
StudentID Result1 Result2 Time (<< column titles)
0001 0 1 5
0002 1 1 4
0003 1 1 5
0004 1 1 5
0005 1 0 3

table 2:
StudentID Result3 Result4 Time
0001 1 1 4
0002 0 0 5
0003 1 0 3
0011 1 1 2
0015 1 1 5

table 3:
StudentID Result5 Result6 Time
0003 1 1 5
0004 0 1 4
0005 0 0 4
0011 1 1 5
0012 1 1 2

cumulative table:
StudentID Result1 Result2 Time_t1 Result3 Result4 Time_t2 Result5
Result6 Time_t3
0001 0 1 5 1 1 4
0002 1 1 4 0 0 5
0003 1 1 5 1 0 3 1 1 5
0004 1 1 5 0 1 4
0005 1 0 3 0 0 4
0011 1 1 2
0012 1 1 2
0015 1 1 5

The process of getting from the separate tables to the accumulated
table shouldn't be too complicated, because the lecturer or their
helpers should be able to do that by themselves using my step-by-step
instructions. If thats not possible it should at least be *easy and
fast* to use for my successor (Bachelor of Computer Science, I guess).
By "connecting " all tables using INNER JOIN (maybe via the Access
assistant) on the StudentID I get great results *as long as all
StudentIDs appear in any of the tables*. But evil woes if one student
is only in the third one...
I hope I got it all more clear now? Is there a way I overlooked to
achieve what I want?

Thanks a lot!

With kind regards,
Chriss

[Smartin]

Re: INNER JOIN argument doubled?

Christian Muenscher wrote:

Dear Sunil!
>
On 27 Feb., 18:03, "Sunil Korah" <kor...@gmail.c omwrote:

>More importantly, I am not clear about what you mean. Which is 'the
>temporary table' that you mention. I cant see any point in connecting
>fields which do not have common values. It would be better if you can
>explain what you are trying to achieve. What information are you
>trying to get out of your tables?

>
The temporary table I mentioned is the table tmpID you introduced some
posts earlier. I will try to explain my problem in detail:
Our students sometimes are having some kind of practical exams in
different sub-subjects. For each of these sub-subjects, I get one
table containing the results of that exam, this time there are seven
tables. The data is being created by scanning the rater forms using a
high-speed duplex document scanner, the Software "TeleForm" outputs
the results after a manual correction cycle. It is possible that some
students only take part in e.g. three of these exams. Now the
lecturers want me to deliver *one* table (best in Excel) containing
the accumulated results of all (seven in this case) exams. Here a
simplyfied example:
>
table 1:
StudentID Result1 Result2 Time (<< column titles)
0001 0 1 5
0002 1 1 4
0003 1 1 5
0004 1 1 5
0005 1 0 3
>
table 2:
StudentID Result3 Result4 Time
0001 1 1 4
0002 0 0 5
0003 1 0 3
0011 1 1 2
0015 1 1 5
>
table 3:
StudentID Result5 Result6 Time
0003 1 1 5
0004 0 1 4
0005 0 0 4
0011 1 1 5
0012 1 1 2
>
cumulative table:
StudentID Result1 Result2 Time_t1 Result3 Result4 Time_t2 Result5
Result6 Time_t3
0001 0 1 5 1 1 4
0002 1 1 4 0 0 5
0003 1 1 5 1 0 3 1 1 5
0004 1 1 5 0 1 4
0005 1 0 3 0 0 4
0011 1 1 2
0012 1 1 2
0015 1 1 5
>
The process of getting from the separate tables to the accumulated
table shouldn't be too complicated, because the lecturer or their
helpers should be able to do that by themselves using my step-by-step
instructions. If thats not possible it should at least be *easy and
fast* to use for my successor (Bachelor of Computer Science, I guess).
By "connecting " all tables using INNER JOIN (maybe via the Access
assistant) on the StudentID I get great results *as long as all
StudentIDs appear in any of the tables*. But evil woes if one student
is only in the third one...
I hope I got it all more clear now? Is there a way I overlooked to
achieve what I want?
>
Thanks a lot!
>
With kind regards,
Chriss
>

Hi again, and PMFJI, but /why/ are you storing results in so many tables?

The question is not meant to be pejorative... If each student can have
1, 2, 3, ... 6 results, this is a perfect one to many relationship! One
table for students, one table for results. If you normalize your data
structure you can easily report who has what result.

--
Smartin