Re: SQL question/puzzle
On 7 Mar 1995, dharper wrote:
} An SQL question/puzzle for the Net:
}
} 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
}
} 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?
}
} Would a different schema make this question easier to answer?
David,
Your schema looks good.
To get the answer, the following should do :
select permit.user_id, relate.co_nbr, stuff.data < or whatever >
from stuff, permit, relate
where permit.user_id = "?"
and relate.co_nbr between permit.co_nbr_lo and co_nbr_hi
and stuff.stuff_id = relate.stuff_id ; Hope this helps, yours,
*********************************
Nick Nobbe, Library of Congress
NLS/BPH
mail: nnob@loc.gov
*********************************