sql statement causing brain pain

[zCrow]

Thanks to all of you who have helped me with other sql queries in the last
couple weeks. And I have another one. I have managed to pretty much get things
working as they should except for one query that I can neither get to work nor
even really get my head around.

This is an sql statement that does work except that it is missing one factor -
dates. I think I can use only 2 tables for this query but perhaps a third is
needed. As you can see from the statement, I am using the Institution and Stats
table, related on the Institution_ID field. What I am looking for are
institutions who have not entered in any data into the stats table for a given
data_year which comes from a dropdown (Stats_Year_ID). The stats table can have
multiple years of data in it and the current statement only works if the
institution has never entered in any data. If they haven't entered in data for
one particular year then there will not be that Stats_Year_ID in the stats
table.

"SELECT Institution_Name, Institution.Institution_ID FROM Institution LEFT
OUTER JOIN Stats ON Stats.Institution_ID = Institution.Institution_ID WHERE
Stats.Institution_ID IS NULL GROUP BY Institution_Name,
Institution.Institution_ID"

Thanks for any help.

[Lionstone]

Don't kick yourself when you see how simple it is. :)

SELECT Institution_Name, Institution.Institution_ID
FROM Institution
LEFT OUTER JOIN Stats ON Stats.Institution_ID =
Institution.Institution_ID AND Stats.data_year=varYear
WHERE Stats.Institution_ID IS NULL
GROUP BY Institution_Name, Institution.Institution_ID

varYear should be equal to Request.Form("Stats_Year_ID"), so do what you
have to do in your setup to get that parameter in there. You can think of
it as joining on a partial table. That is, it's equivalent to

FROM Institution
LEFT OUTER JOIN (SELECT * FROM Stats WHERE data_year=varYear) AS S ON
Institution.Institution_ID=S.Institution_ID

I just find it easier to read with a compound join condition than with a
derived table.



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

> Thanks to all of you who have helped me with other sql queries in the last
> couple weeks. And I have another one. I have managed to pretty much get
> things
> working as they should except for one query that I can neither get to work
> nor
> even really get my head around.
>
> This is an sql statement that does work except that it is missing one
> factor -
> dates. I think I can use only 2 tables for this query but perhaps a third
> is
> needed. As you can see from the statement, I am using the Institution and
> Stats
> table, related on the Institution_ID field. What I am looking for are
> institutions who have not entered in any data into the stats table for a
> given
> data_year which comes from a dropdown (Stats_Year_ID). The stats table can
> have
> multiple years of data in it and the current statement only works if the
> institution has never entered in any data. If they haven't entered in data
> for
> one particular year then there will not be that Stats_Year_ID in the stats
> table.
>
> "SELECT Institution_Name, Institution.Institution_ID FROM Institution LEFT
> OUTER JOIN Stats ON Stats.Institution_ID = Institution.Institution_ID
> WHERE
> Stats.Institution_ID IS NULL GROUP BY Institution_Name,
> Institution.Institution_ID"
>
> Thanks for any help.
>

[zCrow]

Hi again LionStone. I'm glad you hang out here! I haven't actually tried your
sql but looking at it I don't think it will work. The query you sent is looking
for an entry in the stats table where the Stats_Year_ID exists. However, what I
am looking for is where the stats table does not have that Stats_Year_ID as in
"find institutions who have NOT entered any data into the stats table for the
selected data year" Basically I am looking for an entry that doesn't exist.

[Lionstone]

I know.
If there is one row for each year's stats, or even if the row only contains
the year of the last update, that query will work.
It's just like before - the LEFT JOIN keeps all records in the left hand
table, in your case, institutions. The join conditions say to only keep
rows where the institution IDs match (so you're only counting the stats
entered for the proper institution) and where the stats record is from a
certain year. If the stats record is not from that year, the row is
stricken from the result set, BUT the rules of LEFT JOIN say that all
Institution records must be kept (except those later stricken by the WHERE
clause), so only the right-hand table's data is stricken. What does that
mean?
The stats part of your combined record will be NULL if there is no stats
entry for the year you specify, so you can perform the left join and then
ignore all results with a matching stats record, hence the IS NULL condition
in the WHERE clause.


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

> Hi again LionStone. I'm glad you hang out here! I haven't actually tried
> your
> sql but looking at it I don't think it will work. The query you sent is
> looking
> for an entry in the stats table where the Stats_Year_ID exists. However,
> what I
> am looking for is where the stats table does not have that Stats_Year_ID
> as in
> "find institutions who have NOT entered any data into the stats table for
> the
> selected data year" Basically I am looking for an entry that doesn't
> exist.
>

[zCrow]

My apologies for doubting you! I will give that one a go and see if that gives me what I am looking for.

Thanks again for the follow through.

[zCrow]

Hi again LionStone. I hope you aren't feeling that I am taking advantage of
your help but I tried using the sql statement you suggested and it came back
with an error saying that "Join expression is not supported". It seems to have
something to do with the "AND Stats.Stats_Year_ID = varID" bit. Here is the sql
I am using:

"SELECT Institution.Institution_Name, Institution.Institution_ID FROM
Institution LEFT OUTER JOIN Stats ON Stats.Institution_ID =
Institution.Institution_ID AND Stats.Stats_Year_ID = " + request.form("year") +
" WHERE Stats.Institution_ID IS NULL GROUP BY Institution.Institution_Name,
Institution.Institution_ID"

I tried moving that section above on the other side of the WHERE clause and it
no longer gave me an error but of course it didn't give me the correct answer
either.

[Lionstone]

Well, I don't remember what database you're using. First things first -
make sure that Request.Form("year") always exists. A blank there will throw
an error for sure.

Second, try the derived table:
FROM Institution LEFT OUTER JOIN (SELECT * FROM Stats S WHERE
S.Stats_Year_ID = " + Request.Form("year") + ") AS Stats ON
Institution.Institution_ID=Stats.Institution_ID

If the database also doesn't support derived tables, use the IN clause.
This is probably a bit slower, but that depends on the database optimizer.
The general rule is that a join will outperform a subquery for large amounts
of data.

SELECT ID, Name
FROM Institution
WHERE Institution_ID NOT IN (SELECT Institution_ID FROM Stats WHERE
Stats_Year_ID = " + Request.Form("year") + ")"


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

> Hi again LionStone. I hope you aren't feeling that I am taking advantage
> of
> your help but I tried using the sql statement you suggested and it came
> back
> with an error saying that "Join expression is not supported". It seems to
> have
> something to do with the "AND Stats.Stats_Year_ID = varID" bit. Here is
> the sql
> I am using:
>
> "SELECT Institution.Institution_Name, Institution.Institution_ID FROM
> Institution LEFT OUTER JOIN Stats ON Stats.Institution_ID =
> Institution.Institution_ID AND Stats.Stats_Year_ID = " +
> request.form("year") +
> " WHERE Stats.Institution_ID IS NULL GROUP BY
> Institution.Institution_Name,
> Institution.Institution_ID"
>
> I tried moving that section above on the other side of the WHERE clause
> and it
> no longer gave me an error but of course it didn't give me the correct
> answer
> either.
>
>
>

[zCrow]

Once again, thankyou for the extremely thorough followup on this one.

I tried the first one you suggested and while it did not give an error, it
also did not retrieve the records I was looking for. It simply retrieved every
entry in the stats table and then added in the institution that had not entered
anything into the stats table - so basically, it seemed to retrieve all the
records from the institution table.

I then tried the second suggestion and woohoo, that one worked perfectly and
gave me exactly the records I was looking for. I tested it under a few other
circumstances and it worked perfectly.

So, thankyou. Brilliant work, excellent results.

[Lionstone]

I aim to please. :)

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

> Once again, thankyou for the extremely thorough followup on this one.
>
> I tried the first one you suggested and while it did not give an error, it
> also did not retrieve the records I was looking for. It simply retrieved
> every
> entry in the stats table and then added in the institution that had not
> entered
> anything into the stats table - so basically, it seemed to retrieve all
> the
> records from the institution table.
>
> I then tried the second suggestion and woohoo, that one worked perfectly
> and
> gave me exactly the records I was looking for. I tested it under a few
> other
> circumstances and it worked perfectly.
>
> So, thankyou. Brilliant work, excellent results.
>