SQL Query - Get Selected Records - PLS GUIDE

Question

Table Structure –

Table –TblRegister_master

ID	Name	Role	From	To	TotalSeats	Cost	Active
101	Abhijit	Driver	Bandra	CBD Belapur	3	100	1
102	Neha	Passenger	Powoi	Ghansoli	0		1
103	Pranav	Driver	Ghatkopar	Ghansoli	5	500	1
104	Mohit	Passenger	Ghansoli	Thane	0		1
105	Riddhi	Driver	Vikroli	Belapur	1	1000	1
106	Mansi	Passenger	Ghatkoper	Thane	0		1
107	Krunal	Passenger	Airoli	Thane	0		1
108	Heta	Passenger	Vikroli	Ghansoli	0		1
109	Shruti	Driver	Vikroli	Ghansoli	5	1500	1
110	Rohit	Driver	Airoli	Thane	4	1000	1
111	Selvi	Passenger	Airoli	Ghansoli	0		1

 

Table – TblApplied

ID	intDriverID	intPassengerID	Confirmation	Active
1	101	102	N	1
2	101	106	N	1
3	101	107	Y	1
4	101	108	R	1
5	101	104	N	1
6	103	106	N	1
7	103	104	N	1
8	103	102	N	1
9	103	108	N	1
10	110	102	Y	1
11	110	106	Y	1
12	110	107	R	1
13	110	108	Y	1
14	110	111	Y	1

 

intDriverID – Foreign Key to table – TblRegister_master on Column ID and Role – Driver

intPassengerID – Foreign Key to table – TblRegister_master on Column ID and Role – Passenger

Confirmation –N – Pending From Driver, Y- Accepted, R-Rejected

I need the following –

1.       E.g. For Driver “Abhijit”, ID – 101- He has total 3 seats with him. i.e. he can take 3 passengers.

Till now, 5 Passengers have sent him requests.

But, He has accepted only one request – 107 – of Krunal.

So, He has (3-1=2) seats empty.

Hence, while showing Total Rides of theDrivers, I need to show this Driver.

For Driver “Rohit”, ID – 101, He has 4 seats.

Total 5 Passengers requested his ride.

He accepted 4 and rejected 1.

So, since his seats are booked, he should not be displayed in the list.

Hence, I need theoutput as –

How to get this list ?

 

ID	Name	Role	From	To	TotalSeats	Cost	Active
101	Abhijit	Driver	Bandra	CBD Belapur	3	100	1
103	Pranav	Driver	Ghatkopar	Ghansoli	5	500	1
105	Riddhi	Driver	Vikroli	Belapur	1	1000	1
109	Shruti	Driver	Vikroli	Ghansoli	5	1500	1