Results 1 to 13 of 13

Thread: [RESOLVED] SQL Query to eliminate duplicates

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2020
    Posts
    39

    Resolved [RESOLVED] SQL Query to eliminate duplicates

    Ok, newb SQL guy here.

    I have a vb.net mission and one of my queries has me stumped. It can be done in code, but trying to do as much as possible in SQL

    The basic concept is a room booking application. Clients book hotel rooms, need to count how many clients in rooms and how many rooms booked.

    Client booking database:

    RoomRecord| ClientID|.......etc......| RoomWithClientID | .....RoomType|

    Design wise, there is only ever going to be one client rooming with one client, hence not doing 1 to many table (Right or wrong?)

    So a record may look like:

    ClientID | RoomWithClientID |RoomType

    123..........125....................Twin
    124..........0.......................Single
    125..........123....................Twin
    126..........127....................Double
    127..........126....................Double
    128..........0.......................Double
    129..........0.......................Double

    eg - 123 / 125 are partners, same twin room
    124, single person, single room
    However, 128 is a single person who wants a double room.

    I cant just count the number of clients and /2 to get the number of rooms, nor count the number of double rooms and /2, as there may be clients without partners in double rooms.
    eg as above, I have 4 clients in double rooms, however require 3 double rooms as 2 have no partner

    Ideally I want to return DISTINCT records where if ClientID = RoomWithClientID, then we only show one of those 2 ClientID's .
    Then once I have one client record (for both partners), I know I have one room type filled and can count the rooms

    Does that make sense?

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Query to eliminate duplicates

    No it doesn't. Firstly you might want to rethink using the term client when dealing with hotel info... unless you're in the red light district, you don't want clients at your hotel. IF you have to ask what I mean... then uh... it's because it's... too.... formal.... yeah.... formal. Try guests instead. Hotels have guests.

    Second, you can have one guest book multiple rooms, think of a family, the parents are going to book the room, kids may stay in the other. But the booking is under the parent. I've done a bit of traveling, in hotels and such, and never had to give up any other than just my name. It doesn't matter if it's just me, and I get a double, or if it's the four of us and we get two queens. A room is a room.

    Ideally you have a table that contains your rooms, a table that contains your guests, and then a booking table that links the rooms to the guests with dates. From there you can know what rooms are available. As part of the booking record, you can have additional metadata that includes check in information - passport info, driver's license, CC# (for incidentals), total # of guests, etc...

    So, now with that, you can then simply select the rows from the booking table where the guest has checked in, for the given dates that you're looking for... and count them... and boom... there's the number of occupied rooms. Easy, simple.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Member
    Join Date
    Dec 2020
    Posts
    39

    Re: SQL Query to eliminate duplicates

    Sadly Im dealing with the database I have been given and cant do a major redesign on it.
    In this application, there will only be one or 2 clients (and no its not that type of clientele!) . They dont need to book multiple rooms, they will only ever be assigned one room.

  4. #4
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: SQL Query to eliminate duplicates

    Don't you have a unique RoomID? Perhaps you could just select the unique rooms with guests for the rooms booked? And you could try adding the ClientID and RoomWithClientID for the no. of clients?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  5. #5

    Thread Starter
    Member
    Join Date
    Dec 2020
    Posts
    39

    Re: SQL Query to eliminate duplicates

    The application is more for the Travel agents benefit - in that there is a bucket of rooms available (and could be within a range of local accommodation) , and its more about allocating rooms within tour parameters than a specific room booking. With the data Ive got, my only concept is to make one record where (A=B and B=A), making one record AB which is linked to one generic or specific room type

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: SQL Query to eliminate duplicates

    Turn it around!
    Think about it: One "Client" will ever only have one Room.
    But a Room can have one or two (more) clients.

    To me it looks like Room being the Master-Table, and the Client being the Child/Detail.
    So you would need a Foreign Key in the Client-Table pointing to the Primary Key of the Room

    If a Room is a single, Twin, Double, Broom-Cupboard or whatever is just a category defining "available" beds (or in techn. terms: capacity)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    Banned
    Join Date
    Jan 2021
    Location
    USA
    Posts
    25

    Re: SQL Query to eliminate duplicates

    To remove duplicate rows from your database sets it is important to include the distinct keyword in your select statement. Sample SQL query to do this is:

    Row_Number. WITH CTE (Col1, Col2, Col3, DuplicateCount) AS ( SELECT Col1, Col2, Col3, ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3 ORDER BY Col1) AS DuplicateCount FROM MyTable ) SELECT * from CTE Where DuplicateCount = 1.

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Query to eliminate duplicates

    you need just rooms right? Not clients?
    Ugh.... easy... first select the single rooms:
    Code:
    Select clientId from yourTable where RoomWithClientId = 0
    That gets you all the singles...
    OK, now get the double occupancy ones:
    Code:
    select ClientId from yourTable Where RoomWithClientId <> 0


    aaaah.... wait.... even better...

    Code:
    select * from yourtable where ClientId not in (select RoomWithClientId from yourTable)
    Try that last one... see if it gives you what you're after...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Member
    Join Date
    Dec 2020
    Posts
    39

    Re: SQL Query to eliminate duplicates

    This is the result of that SQL:
    Code:
    WITH CTE (Col1, Col2,  DuplicateCount) AS 
    (SELECT ClientID, RoomWithID, ROW_NUMBER() OVER(PARTITION BY ClientID, RoomWithID ORDER BY ClientID) 
    AS DuplicateCount FROM TourData where tourID=73) SELECT * from CTE Where DuplicateCount = 1
    Attachment 180336

    Try that last one... see if it gives you what you're after...

    -tg
    Unfortunately as the ID exists in both columns, they cancel each other out and youll just get the 0's
    I definitely take on board that it would be more logical to have a RoomID - then both Clients would have the same ID making it easier to manage

  10. #10
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: SQL Query to eliminate duplicates

    Quote Originally Posted by SpookyAwol View Post
    I definitely take on board that it would be more logical to have a RoomID - then both Clients would have the same ID making it easier to manage
    That was what I was asking in post #4, having a unique RoomID should help.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  11. #11

    Thread Starter
    Member
    Join Date
    Dec 2020
    Posts
    39

    Re: SQL Query to eliminate duplicates

    Decided on a simple hack for now, added a column with a '0.5' decimal per Client when in same room, and '1' when they are single

  12. #12
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Query to eliminate duplicates

    Yeah, it would.... but it sounds like he's dealing with an existing design here, and that's not an option... for what ever reason.
    I've looked at a number of ways... and with out that roomId, or some other redesign, I'm not seeing it. I thought I had an idea with flipping the two client ids around, but thaat also fell apart really quick when I ran it through. Bottom line, the design sucks, and I don't see a way to get the result being sought.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13

    Thread Starter
    Member
    Join Date
    Dec 2020
    Posts
    39

    Re: SQL Query to eliminate duplicates

    Pretty much
    Short of redesigning the database (and dealing with previous existing records) it gets messy real quick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width