Help with Complex joins

[Greg Gillis]

I need some help. Here is the problem.

Table_1 (gl_Train_KeyIdeas)

+----------------------+----------------+-------------------+---------------+
| KeyIdea_ID | Unit_ID | Group_ID | Title |
+----------------------+----------------+-------------------+---------------+
| 1 | 1 | 27 | yada 1 |
| | | | |
| 2 | 1 | 27 | yada 2 |
| | | | |
| 3 | 1 | 27 | yada 3 |
| | | | |
| 4 | 1 | 27 | yada 4 |
| | | | |
+----------------------+----------------+-------------------+----------------+

Table_2 (gl_Train_Progress)

+----------------------+----------------+----------------------+
| ID | User_ID | KeyIdea_ID |
+----------------------+----------------+----------------------+
| 12 | 5 | 3 |
| | | |
| 11 | 5 | 2 |
| | | |
| 10 | 5 | 1 |
| | | |
+----------------------+----------------+-----------------------+
The following sql returns field KeyIdea_ID = 4 which is the only
KeyIdea not in both tables.

SELECT gl_Train_KeyIdeas.KeyIdea_ID
FROM gl_Train_KeyIdeas LEFT JOIN gl_Train_Progress ON
gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID
WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND
((gl_Train_KeyIdeas.Unit_ID)=1));

What I seem to be having trouble with is specifying the User_ID in
table 2. I need to specify the current user for example: an sql with a
User_ID = 6 would return KeyIdea_ID of 1, 2, 3, and 4.

Any idea's?

[Greg Gillis]

I figured it out.

I used the following sql

SELECT gl_Train_KeyIdeas.KeyIdea_ID
FROM gl_Train_KeyIdeas
LEFT JOIN gl_Train_Progress
ON gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID
AND gl_Train_Progress.User_ID = 6
WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND
((gl_Train_KeyIdeas.Unit_ID)=1));

On Mon, 05 Apr 2004 18:35:35 GMT, ms***@rcsys.net (Greg Gillis) wrote:

I need some help. Here is the problem.

Table_1 (gl_Train_KeyIdeas)

+----------------------+----------------+-------------------+---------------+
| KeyIdea_ID | Unit_ID | Group_ID | Title |
+----------------------+----------------+-------------------+---------------+
| 1 | 1 | 27 | yada 1 |
| | | | |
| 2 | 1 | 27 | yada 2 |
| | | | |
| 3 | 1 | 27 | yada 3 |
| | | | |
| 4 | 1 | 27 | yada 4 |
| | | | |
+----------------------+----------------+-------------------+----------------+

Table_2 (gl_Train_Progress)

+----------------------+----------------+----------------------+
| ID | User_ID | KeyIdea_ID |
+----------------------+----------------+----------------------+
| 12 | 5 | 3 |
| | | |
| 11 | 5 | 2 |
| | | |
| 10 | 5 | 1 |
| | | |
+----------------------+----------------+-----------------------+
The following sql returns field KeyIdea_ID = 4 which is the only
KeyIdea not in both tables.

SELECT gl_Train_KeyIdeas.KeyIdea_ID
FROM gl_Train_KeyIdeas LEFT JOIN gl_Train_Progress ON
gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID
WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND
((gl_Train_KeyIdeas.Unit_ID)=1));

What I seem to be having trouble with is specifying the User_ID in
table 2. I need to specify the current user for example: an sql with a
User_ID = 6 would return KeyIdea_ID of 1, 2, 3, and 4.

Any idea's?

[Greg Gillis]

I figured it out.

I used the following sql

SELECT gl_Train_KeyIdeas.KeyIdea_ID
FROM gl_Train_KeyIdeas
LEFT JOIN gl_Train_Progress
ON gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID
AND gl_Train_Progress.User_ID = 6
WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND
((gl_Train_KeyIdeas.Unit_ID)=1));

On Mon, 05 Apr 2004 18:35:35 GMT, ms***@rcsys.net (Greg Gillis) wrote:

I need some help. Here is the problem.

Table_1 (gl_Train_KeyIdeas)

+----------------------+----------------+-------------------+---------------+
| KeyIdea_ID | Unit_ID | Group_ID | Title |
+----------------------+----------------+-------------------+---------------+
| 1 | 1 | 27 | yada 1 |
| | | | |
| 2 | 1 | 27 | yada 2 |
| | | | |
| 3 | 1 | 27 | yada 3 |
| | | | |
| 4 | 1 | 27 | yada 4 |
| | | | |
+----------------------+----------------+-------------------+----------------+

Table_2 (gl_Train_Progress)

+----------------------+----------------+----------------------+
| ID | User_ID | KeyIdea_ID |
+----------------------+----------------+----------------------+
| 12 | 5 | 3 |
| | | |
| 11 | 5 | 2 |
| | | |
| 10 | 5 | 1 |
| | | |
+----------------------+----------------+-----------------------+
The following sql returns field KeyIdea_ID = 4 which is the only
KeyIdea not in both tables.

SELECT gl_Train_KeyIdeas.KeyIdea_ID
FROM gl_Train_KeyIdeas LEFT JOIN gl_Train_Progress ON
gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID
WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND
((gl_Train_KeyIdeas.Unit_ID)=1));

What I seem to be having trouble with is specifying the User_ID in
table 2. I need to specify the current user for example: an sql with a
User_ID = 6 would return KeyIdea_ID of 1, 2, 3, and 4.

Any idea's?