-
Feb 25th, 2021, 04:58 PM
#1
Thread Starter
Member
[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?
-
Feb 25th, 2021, 05:37 PM
#2
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
-
Feb 25th, 2021, 05:51 PM
#3
Thread Starter
Member
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.
-
Feb 25th, 2021, 08:50 PM
#4
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?
-
Feb 25th, 2021, 09:44 PM
#5
Thread Starter
Member
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
-
Feb 26th, 2021, 01:57 AM
#6
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
-
Feb 26th, 2021, 07:34 AM
#7
Banned
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.
-
Feb 26th, 2021, 08:03 AM
#8
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
-
Feb 26th, 2021, 03:35 PM
#9
Thread Starter
Member
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
-
Feb 26th, 2021, 06:03 PM
#10
Re: SQL Query to eliminate duplicates
Originally Posted by SpookyAwol
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.
-
Feb 26th, 2021, 08:14 PM
#11
Thread Starter
Member
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
-
Feb 26th, 2021, 08:19 PM
#12
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
-
Feb 26th, 2021, 08:24 PM
#13
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|