Outer Joining Multiple Tables

I have 3 tables that I want to join like so...

table 1:
USER
---------
uid
first_name
last_name


table 2:
SIGNOFF
--------------
uid
item_name


table 3:
COMPLETED
--------------------
user_uid
signoff_uid
date_complete


If I use table 3 to join them like so everyone signed off for UID 1 of
the signoff, I can do this:

SELECT s.item_name, u.first_name, u.last_name, c.date_completed
FROM user u, signoff s, completed c
WHERE u.uid = c.user_uid
AND c.signoff_uid = s.uid
AND s.uid = 1

What I'd like to do is list all employees regardless of their signoff
status. Nothing will be in the COMPLETED table though for their id. I
don't know how to do an outer join "once removed" like that.

Thanks for any help.

D.

DonO wrote:
>I have 3 tables that I want to join like so...
>
> table 1:
> USER
> ---------
> uid
> first_name
> last_name
>
>
> table 2:
> SIGNOFF
> --------------
> uid
> item_name
>
>
> table 3:
> COMPLETED
> --------------------
> user_uid
> signoff_uid
> date_complete
>
>
> If I use table 3 to join them like so everyone signed off for UID 1 of
> the signoff
The above makes no sense???

> I can do this:
>
> SELECT s.item_name, u.first_name, u.last_name, c.date_completed
> FROM user u, signoff s, completed c
> WHERE u.uid = c.user_uid
> AND c.signoff_uid = s.uid
> AND s.uid = 1
>
> What I'd like to do is list all employees regardless of their signoff
> status.
SELECT
*
FROM user

As a general rule, always use explict JOINs with their own join criteria
rather than implicit comma joins where the join criteria gets mixed in with
the WHERE clauses.

Ok. I'll try to explain better...

This will work to get everyone who is in the system:

SELECT signoff.item_name, user.first_name, user.last_name,
completed.date_complete
FROM signoff
JOIN completed ON completed.signoff_uid = signoff.uid
JOIN user ON user.uid = complete.user_uid
WHERE signoff.uid = '1' ;

That gets me the name off the required signoff, the user's name, and
the date they signed off.

I'd like to list all the employee's that have not signed off too with
a NULL for the date_complete.

Thanks,
D.

On Jan 7, 4:45 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> DonO wrote:
> >I have 3 tables that I want to join like so...
>
> > table 1:
> > USER
> > ---------
> > uid
> > first_name
> > last_name
>
> > table 2:
> > SIGNOFF
> > --------------
> > uid
> > item_name
>
> > table 3:
> > COMPLETED
> > --------------------
> > user_uid
> > signoff_uid
> > date_complete
>
> > If I use table 3 to join them like so everyone signed off for UID 1 of
> > the signoff
>
> The above makes no sense???
>
> > I can do this:
>
> > SELECT s.item_name, u.first_name, u.last_name, c.date_completed
> > FROM user u, signoff s, completed c
> > WHERE u.uid = c.user_uid
> > AND c.signoff_uid = s.uid
> > AND s.uid = 1
>
> > What I'd like to do is list all employees regardless of their signoff
> > status.
>
> SELECT
> *
> FROM user
>
> As a general rule, always use explict JOINs with their own join criteria
> rather than implicit comma joins where the join criteria gets mixed in with
> the WHERE clauses.

DonO wrote:
> Ok. I'll try to explain better...
>
> This will work to get everyone who is in the system:
>
> SELECT signoff.item_name, user.first_name, user.last_name,
> completed.date_complete
> FROM signoff
> JOIN completed ON completed.signoff_uid = signoff.uid
> JOIN user ON user.uid = complete.user_uid
> WHERE signoff.uid = '1' ;
>
> That gets me the name off the required signoff, the user's name, and
> the date they signed off.
>
> I'd like to list all the employee's that have not signed off too with
> a NULL for the date_complete.
>
> Thanks,
> D.
>

Please do not top post.

Can you supply some context for these tables?
What is signoff.item_name?
What is the significance of signoff.uid = '1'?
If the above query gets "everyone who is in the system", how can there be
more employees (note no apostrophe) to get?