SQL help on joins

[zCrow]

I'm getting bogged down in figuring out how to do joins on 3 tables. The three
tables are Institution, stats and signoff. Institution_ID is the relationship
between all tables. What I need to accomplish is I want to display records
where an institution has entered in some data into the stats table but has not
completed entering all data and therefore would not have an entry in the
signoff table (Institution_ID IS NULL).

I can do an outer join on two of the tables but as soon as I introduce the
third, it just doesn't work. Can anyone suggest what I need to do to make this
work?

Thanks.

[Lionstone]

FROM Institution I INNER JOIN Stats S ON I.Institution_ID = S.Institution_ID
LEFT OUTER JOIN Signoff O ON I.Institution_ID=O.Institution_ID
WHERE O.Institution_ID IS NULL

You'll have to use either SELECT DISTINCT or the GROUP BY clause to get each
institution, else you'll have it listed once for each record in the stats
table.

Alternatively:
FROM Institution I LEFT OUTER JOIN Signoff O ON
I.Institution_ID=O.Institution_Id
WHERE O.Institution_ID IS NULL AND I.Institution_ID IN (SELECT DISTINCT
Institution_ID FROM Stats)

In this case, a normal SELECT is fine.


"zCrow" <webforumsuser@macromedia.com> wrote in message
news:d5oand$fij$1@forums.macromedia.com...

> I'm getting bogged down in figuring out how to do joins on 3 tables. The
> three
> tables are Institution, stats and signoff. Institution_ID is the
> relationship
> between all tables. What I need to accomplish is I want to display records
> where an institution has entered in some data into the stats table but has
> not
> completed entering all data and therefore would not have an entry in the
> signoff table (Institution_ID IS NULL).
>
> I can do an outer join on two of the tables but as soon as I introduce the
> third, it just doesn't work. Can anyone suggest what I need to do to make
> this
> work?
>
> Thanks.
>

[zCrow]

Lionstone - again you are there with sql help. You helped me out last week as well. I haven't tried it yet but will shortly.

Thanks for taking the time for doing this.

[Lionstone]

Not a problem.

"zCrow" <webforumsuser@macromedia.com> wrote in message
news:d5qje1$rsb$1@forums.macromedia.com...

> Lionstone - again you are there with sql help. You helped me out last week
> as well. I haven't tried it yet but will shortly.
>
> Thanks for taking the time for doing this.