HOW TO JOIN 2 tables in SQL Server 2008

Question

I have 2 tables dbo.seat and dbo.booking

dbo.seat

noSeat
------
1-2
1-3
1-4
1-8
4-2
4-3
4-4
5-1
5-2

dbo.booking

noBooking   |  noSeat  | statusBooked |       endTime
1002           1-4            0         2015-02-16 13:30:00.000
1003           1-4            1         2015-02-17 13:30:00.000
1004           1-8            1         2015-02-17 13:30:00.000
1005           1-2            0         2015-02-16 14:59:00.000
1006           1-3            0         2015-02-16 14:59:00.000

How to take seat.noSeat which is not booked ?

This sample show us that seat number 1-4 and 1-8 is booked. and I want to know what seat is available(the output is all seat.noSeat except 1-4 and 1-8) ?

Table booking is for booking transaction and seat is list of seat provided.

UPDATE :

statusBooked will automatically update to 0 when endTime is equal CurrentTime. it mean it's over and tell us that 1-4 is available until someone booked it again (insert method to dbo.booking statusBooked by default 1)

Use Inner Join between two table on "noseat" and add "WHERE" clause as "StatusBooked <> 1" — Paresh J
– Paresh J, Commented Feb 17, 2015 at 4:12
You have bookings that are not booking seats (statusBooked = 0) and seat 1-4 is both booked and not booked? — James Z
– James Z, Commented Feb 17, 2015 at 4:14
@JamesZ it's for status is that seat still booked or has been available. it means that seat 1-4 was booked by someone and now is booked again by someone else. 0 is available 1 is booked — azalikaEriya
– azalikaEriya, Commented Feb 17, 2015 at 4:23
@PareshJ I try this SELECT s.noSeat FROM seat s INNER JOIN booking b ON s.noSeat = b.noSeat WHERE b.statusBooked <> 1 and the result is what is in booking table. — azalikaEriya
– azalikaEriya, Commented Feb 17, 2015 at 4:26
@azalikaEriya What do you want is still unclear. Can you update your question with expected result? That would help people here to answer. — Jenish Rabadiya
– Jenish Rabadiya, Commented Feb 17, 2015 at 4:52

shawnt00 · Accepted Answer · 2015-02-17 04:43:27Z

2

select *
from dbo.seat
where noseat not in (
    select noseat
    from dbo.booking
    where statusbooking = 1
)

Here's my guess. I can't say I really understand what "noseat" means (unless it's "number of seat")?

answered Feb 17, 2015 at 4:43

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

azalikaEriya Over a year ago

number of seat. yes you are right. it works. but is it oke for any case in future maybe ? @shwant00

shawnt00 Over a year ago

As far as I understand it. If there's a row with statusBooking equal to 1 then that seat should be excluded from your results. That's pretty much all it does.

shawnt00 Over a year ago

You should probably use an explicit select list but there must be another problem in the c# code.

shawnt00 Over a year ago

It would be best to supply full error message if you still want assistance with that. It sounds like that's a separate question though.

Collectives™ on Stack Overflow

HOW TO JOIN 2 tables in SQL Server 2008

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related