Forum Moderators: open
trip:
trip_id, seats [int]
trip_user:
trip_id, user_id, confirmed [enum ('y','n')]
The basic idea is that one user will create trips, and then other users will become associated with that trip. Then the owner of the trip and confirm or deny these other users. Initially a trip will have x number of seats available. Here might be some sample data:
trip:
1, 4
2, 3
trip_user
1, 1004, 'y'
1, 1003, 'n'
1, 1007, 'y'
1, 1005, 'n'
2, 1006, 'n'
I'd like to get a listing of all the trips, and the remaining amount of seats:
result (first trip has two seats available, and second has three):
1, 2
2, 3
I'm pretty inexperienced when it comes to this, but this is what I have:
select t.trip_id, t.seats - count(t_u.confirmed) remaining from trip t, trip_user t_u where t_u.confirmed = 'y' and t.trip_id = t_u.trip_id group by t_u.trip_id;This successfully joins the tables and tells me the remaining seats for the trips. The problem is that it doesn't show me the trips for which there are no confirmed trip_users.
Ideally I'd like to do something like the following:
Group the trip_users by trip_id and then -only- count items in "confirmed" if they are "y."
Is there any way to do this? Thanks!
create table tTrip (
trip_id INT NOT NULL,
seats INT NOT NULL)
create table tUser (
user_id INT NOT NULL,
user_name VARCHAR(64))
create table tTripUser (
trip_id INT NOT NULL,
user_id INT NOT NULL,
confirmed CHAR NOT NULL)
insert into tuser (user_id,user_name) values (1001,'user1')
insert into tuser (user_id,user_name) values (1002,'user2')
insert into tuser (user_id,user_name) values (1003,'user3')
insert into tuser (user_id,user_name) values (1004,'user4')
insert into tuser (user_id,user_name) values (1005,'user5')
insert into tuser (user_id,user_name) values (1006,'user6')
insert into tuser (user_id,user_name) values (1007,'user7')
insert into tuser (user_id,user_name) values (1008,'user8')
insert into ttrip (trip_id, seats) values (1, 4)
insert into ttrip (trip_id, seats) values (2, 3)
insert into ttrip (trip_id, seats) values (3, 4)
insert into ttrip (trip_id, seats) values (4, 4)
insert into ttripuser (trip_id,user_id, confirmed) values (1, 1004, 'Y' )
insert into ttripuser (trip_id,user_id, confirmed) values (1, 1003, 'N' )
insert into ttripuser (trip_id,user_id, confirmed) values (1, 1007, 'Y' )
insert into ttripuser (trip_id,user_id, confirmed) values (1, 1005, 'N' )
insert into ttripuser (trip_id,user_id, confirmed) values (2, 1006, 'N' )
I rewrote your query to give you almost anything you wanted to know regarding all trips setup, regardless of whether they have any registered users (confirmed or unconformed) or NO users registered. Hope it helps in MySQL:
select t.trip_id, t.seats,
(select count(*) from tTripUser t2 where t.trip_id = t2.trip_id and t2.confirmed = 'Y') ReservedAndConfirmed,
(select count(*) from tTripUser t3 where t.trip_id = t3.trip_id and t3.confirmed = 'N') ReservedAndUnconfirmed,
(select t.seats-count(*) from tTripUser t4 where t.trip_id = t4.trip_id and t4.confirmed = 'Y') RemainingUnreservedOrUnconfirmed
from tTrip t
I get the following output when I run the above query in SQL*Server:
trip_id seats ReservedAndConfirmed ReservedAndUnconfirmed RemainingUnreservedOrUnconfirmed
----------- ----------- -------------------- ---------------------- --------------------------------
1 4 2 2 2
2 3 0 1 3
3 4 0 0 4
4 4 0 0 4
(4 row(s) affected)
First column is trip_id.
Second column is the total number of seats on the trip.
Third column is the number of users which have reserved a seat AND confirmed it.
Forth column is the number of users which have reserved a s seat and NOT confirmed it.
Fifth column is the number of seats that are available (both registered seats unconfirmed or unregistered seats).
[edited by: ZydoSEO at 3:12 am (utc) on Dec. 24, 2007]
Sorry to keep you waiting! I've been busy working on other parts of the application, but I just played around with some of your subqueries and they're working beautifully. I'm able to get exactly the data I need. I ended up using something like this for the front page:
select t.trip_id, t.seats - (select count(trip_id) from trip_user t_u where t.trip_id = t_u.trip_id and confirmed = 'y') seats from trip t;But the basic setup you gave me is very flexible and I'm sure I'll be able to use in the future.
Again, thanks so much for your help!
