SQL Join, Joining Two Tables

Question

Post reply

SQL Join, Joining Two Tables

TheOxblood

Mr or Mrs. 500

Points: 538
More actions
September 27, 2011 at 10:16 pm

#244590

I have two columns, the ROOM and CLIENTROOM,
I want to select all rooms which is covered by my WHERE CLAUSE..
[/URL]
[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ratheesh@SQL Grasshopper Points: 19 More actions · Answer 1

Not able to understand your question. Can you detail your issue?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

What is happening when you include that where clause that is not to your expectation?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TheOxblood Mr or Mrs. 500 Points: 538 More actions · Answer 3

I want to select all available room on a specific time..

WHERE ((ClientRoom.ArrivalDate NOT BETWEEN '2011-09-28 8:00:00' AND '2011-09-28 10:00:00') AND

(ClientRoom.DepartureDate NOT BETWEEN '2011-09-28 8:00:00' AND '2011-09-28 10:00:00')))

TheOxblood Mr or Mrs. 500 Points: 538 More actions · Answer 4

If I include the where clause on the query, It only filters the CLIENTROOM Table,

When I dont inlcude it.. It returns all ROOMs without filters.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Does the Rooms table have arrival dates and departure dates? You could also try throwing on the where clause:

or ClientRoom.ArrivalDate is null

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 6

Then I think you simply have to add

OR ClientRoom.ClientRoomNo IS NULL

for rooms that have no reservations at all. You can use any other column in ClientRoom for that matter to check the NULL.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

TheOxblood Mr or Mrs. 500 Points: 538 More actions · Answer 7

NO it dos not have...

ROOM TABLE

PK - RoomNo

- RoomTypeNo

- RoomStatusId

CLIENTROOM TABLE

PK - ClientRoomNo

FK - RoomNo

- ArrivalDate

- DepartureDate

TheOxblood Mr or Mrs. 500 Points: 538 More actions · Answer 8

Ronald H (9/27/2011)
Then I think you simply have to add
OR ClientRoom.ClientRoomNo IS NULL
for rooms that have no reservations at all. You can use any other column in ClientRoom for that matter to check the NULL.

I cant check it on the ClientRoom Table because it onlycontains the

PK - ClientRoomID

FK - RoomNo

- ArrivalDate

- DepartureDate

yubo1 SSChasing Mays Points: 612 More actions · Answer 9

yubo1

SSChasing Mays

Points: 612

September 27, 2011 at 11:50 pm

#1387549

inner join instead of left outer join

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Please provide sample data so we can test solutions.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events