Post reply

SQL Join, Joining Two Tables

  • 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]

  • September 27, 2011 at 10:47 pm

    #1387528

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

  • September 27, 2011 at 10:58 pm

    #1387531

    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

  • September 27, 2011 at 11:20 pm

    #1387537

    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')))

  • September 27, 2011 at 11:23 pm

    #1387538

    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.

  • September 27, 2011 at 11:26 pm

    #1387539

    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

  • September 27, 2011 at 11:29 pm

    #1387542

    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

  • September 27, 2011 at 11:32 pm

    #1387544

    NO it dos not have...

    ROOM TABLE

    PK - RoomNo

    - RoomTypeNo

    - RoomStatusId

    CLIENTROOM TABLE

    PK - ClientRoomNo

    FK - RoomNo

    - ArrivalDate

    - DepartureDate

  • September 27, 2011 at 11:34 pm

    #1387545

    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

  • September 27, 2011 at 11:50 pm

    #1387549

    inner join instead of left outer join

  • September 27, 2011 at 11:56 pm

    #1387550

    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

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

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