Query returning 3 repeated records per ID?

Charlie2

Registered User.
Local time
Today, 22:16
Joined
Mar 28, 2002
Messages
79
I am trying to build a query which will find the costs from a set of tables where the client has costs.

The client has a cost on booking a fee, he has a hotel room cost and a room facility cost.

My query brings up what I want but each result is repeated 3 times.

I don't know why, can someone correct me please.

Code:
SELECT DISTINCT Addresses.ID, Holiday_Bookings.Initial_Cost, Room_Facilities.Cost, Rooms.BasicCostPerNight
FROM ((Room_Facilities INNER JOIN ((Hotels INNER JOIN Rooms ON Hotels.HotelID = Rooms.HotelID) INNER JOIN Stops ON Hotels.HotelID = Stops.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID) INNER JOIN (Routes INNER JOIN WalkTypes ON Routes.RouteID = WalkTypes.RouteID) ON Stops.StopID = Routes.Arrive_In) INNER JOIN (Holiday_Bookings INNER JOIN (Groups INNER JOIN (Clients INNER JOIN Addresses ON Clients.ID = Addresses.ID) ON Groups.ID = Clients.GroupID) ON Holiday_Bookings.ID = Clients.ClientID) ON WalkTypes.Walk_TypeID = Groups.GroupID;
 
You have several tables in the query from which no data is selected. They may be causing the problem. Remember, in a 1-m relationship, you'll get as many rows as there are matches in the m-side table. If you don't select anything from the m-side table, the results will look as if they are duplicated.
 
Thanks

I have altered my query to remove the unneeded tables and now I get 6 results instead of one.

I am puzzled.

Code:
SELECT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.BasicCostPerNight
FROM Holiday_Bookings, Room_Facilities INNER JOIN Rooms ON Room_Facilities.FacilityID = Rooms.FacilityID;

Sorry added DISTINCT and am back to three.
 
Last edited:
Sorted

Ahh I think I have sorted it out.

Thanks again Pat ;)

Code:
SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.BasicCostPerNight
FROM Holiday_Bookings RIGHT JOIN (Room_Facilities INNER JOIN (Hotels INNER JOIN Rooms ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID) ON Holiday_Bookings.ClientID = Rooms.ClientID;
 

Users who are viewing this thread

Back
Top Bottom