Creating a Select statement with subqueries to 3 other tables...

[Wenin]

I have four total tables.
Table One (Documents)- List of Documents. Each record has two fields
related to this issue. First field (Document_ID) is the ID of the
document, second field is the ID of the record (Task_ID) it is
associated to in Table Two.

Table Two (Activities)- List of activities. Each record has two fields
related to this issue. First field (Activity_ID) is the ID of the
activity, the second field (Group_ID) is the ID of the record it is
associated to in Table Three.

Table Three (Groups) - List of groups. Each record has two fields
related to this issue. First field (Group_ID) is the ID of the group,
the second field (Stage_ID) is the ID of the record it is associated to
in Table four.

Table Four (Stages)- List of Event Stages. Each record has two fields
that is related to this issue. The first field (Stage_ID) is the ID of
the stage of an event, the second record is the ID number associated to
the event. This last ID is a known value.

20000024 = the Event ID

I'm trying to come up with a list of Documents from the first table
that is associated to an Event in the Fourth table.

Query Analyzer shows no errors within the script. It just doesn't
return any data. I know that it should, if it does what I'm wanting it
to do.

SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE
(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID
= A.Activity_ID))))))= '20000024')

[Wenin]

Wenin wrote:

I have four total tables.
Table One (Documents)- List of Documents. Each record has two fields
related to this issue. First field (Document_ID) is the ID of the
document, second field is the ID of the record (Task_ID) it is
associated to in Table Two.

Table Two (Activities)- List of activities. Each record has two fields related to this issue. First field (Activity_ID) is the ID of the
activity, the second field (Group_ID) is the ID of the record it is
associated to in Table Three.

Table Three (Groups) - List of groups. Each record has two fields
related to this issue. First field (Group_ID) is the ID of the group, the second field (Stage_ID) is the ID of the record it is associated to in Table four.

Table Four (Stages)- List of Event Stages. Each record has two fields that is related to this issue. The first field (Stage_ID) is the ID of the stage of an event, the second record is the ID number associated to the event. This last ID is a known value.

20000024 = the Event ID

I'm trying to come up with a list of Documents from the first table
that is associated to an Event in the Fourth table.

Query Analyzer shows no errors within the script. It just doesn't
return any data. I know that it should, if it does what I'm wanting it to do.

SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE (C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID = A.Activity_ID))))))= '20000024')

I'm thinking this is going to require the use of Joins, but I still
can't seem to wrap my head around how joins work exactly.

[Thomas R. Hummel]

First, to save Joe Celko from having to post...

Rows are not records, columns are not fields. You are thinking in the
old sequential file processing mentality. It will take you a year to
unlearn this and get your mind accustomed to thinking in a set-oriented
fashion.

Ok, now that that's out of the way...

You are correct, it will require the use of joins. Please don't take
this as an insult, as I'm sure that you are still learning SQL and
there was a time for all of us when we didn't know it, but this is a
pretty simple set of joins. You really do need to "get your head around
how joins work exactly" if you are going to be doing any SQL coding.
There are plenty of good books for SQL beginners out there. Even the
SQL for Dummies book should get you past this hurdle.

A join takes each table being joined, creates a cartesian product of
the two (i.e., every possible combination of records) then filters that
result based on the ON part of the join. As an example, if I have two
tables:

Customers
cust_id
1
2

Orders
ord_id cust_id
1 1
2 2
3 1

The cartesian product would be:
cust_id ord_id cust_id
1 1 1
1 2 2
1 3 1
2 1 1
2 2 2
2 3 1

If I joined these tables on cust_id = cust_id then it would give me:
cust_id ord_id cust_id
1 1 1
1 3 1
2 2 2

Without the join criteria the cartesian product is useless, but it is
the starting point.

Another way to think of it is this... I know my two tables are related
and I know the column that relates them. Therefore I can join on that
column. That is a VERY simplistic approach to joins, but it will
hopefully point you in the right direction.

Good luck,
-Tom.