Re: SQL question/puzzle
>
> Assume the following tables:
>
> STUFF (stuff_id, data....)
> stuff_id - unique ID for this record/tuple.
> data - the interesting part for a user.
>
> RELATE (stuff_id, co_nbr)
> stuff_id - foreign key into STUFF
> co_nbr - a valid company number for this STUFF record.
>
> STUFF one-to-many with RELATE. i.e, there can be several
> company numbers associated with a given STUFF record. (1-5)
>
> PERMIT (user_id, co_nbr_lo, co_nbr_hi)
> user_id - login ID for a user.
> co_nbr_lo - lowest company number for this range
> co_nbr_hi - highest company number for this range
>
I would suggest that instead you lay this out as
PERMIT (user_id, co_nbr)
and have multiple entries comprising each range, especially if you could
have multiple ranges anyway. You end up with more rows, but they will be
easier to maintain and more flexible.
> There will be many PERMIT records for the same user_id
> with different company number ranges. (5-20)
>
>
> Finally, the question: Given a user_id, how to find all STUFF
> records this user's company number ranges allows her access to?
>
Using the one co_nbr per PERMIT row you could use either
select stuff.data from stuff, relate
where stuff.stuff_id = relate.stuff_id
and relate.co_nbr in (
select permit.co_nbr from permit
where permit.user_id = v_this_id);
or
select stuff.data from stuff, relate, permit
where stuff.stuff_id = relate.stuff_id
and relate.co_nbr = permit.co_nbr
and permit.user_id = v_this_id;
If you want to continue to use ranges you could go to a stored
procedure or 4gl and do something like
FOREACH c_select FOR
SELECT permit.co_nbr_lo, permit.co_nbr_hi
INTO v_co_nbr_lo, v_co_nbr_hi
FROM permit
WHERE permit.user_id = v_this_id
BEGIN
SELECT stuff.data FROM stuff, relate
WHERE stuff.stuff_id = relate.stuff_id
AND relate.co_nbr BETWEEN v_co_nbr_lo and v_co_nbr_hi;END FOREACH
I don't think there is a way to select the ranges in straight SQL.
> Would a different schema make this question easier to answer?
>
I think the single co_nbr version is easier to maintain and more
flexible.
Of course if the ranges in the co_nbr_lo, co_nbr_hi version of PERMIT
can overlap and you WANT to select the stuff.data more than once if they
do then you would need to either stick with the ranges, or add another
column to the single co_nbr version of PERMIT that would make them
unique rows. Perhap range_nbr? That would give you
PERMIT(range_nbr, user_id, co_nbr).
> - David dharper@neosoft.com
>
--
-- Kai. \\\\//_
kaih@courtlink.utcourts.gov Live long and prosper.
#include <st_disclaim.h>