I have a Session table:
Username Type
------------------
bob private
bob private
sally private
bob duet
And I have an event table
Username Date Type
---------------------------
bob 1/1/04 private
bob 1/2/04 private
bob 1/3/04 private
bob 1/3/04 duet
bob 1/3/04 duet
The way it works is this. A client signs up for appointments, and
they are placed in the event table. They buy sessions whenever they
want. The session table works like a clearing account. When they buy
sessions, rows are added to the session table, and when they use
sessions, rows are deleted. If they have an appointment, but no
sessions left, then they need to buy more. They can and will be
signed up for more appointments then sessions that they have bought.
I somehow need to join these tables, to show whether a client needs
to pay for an appointment or not. So the result set I want is:
Username Date Type Pay?
-----------------------------------
bob 1/1/04 private No
bob 1/2/04 private No
bob 1/3/04 private Yes
bob 1/3/04 duet No
bob 1/3/04 duet Yes
I can't actually delete sessions from the session table since this is
dataset is for display purposes, and the session still need to exist.
Does anybody have any ideas?
Thank you,
Ben