Selecting multiple rows of many to many related columns into one column in SQL SERVER?

Question

I have the following DB Tables with SQL Server

Booking(bookingID,customerName,branchID,RefNumber,...)

Trip(TripID,vehicleID,...)

BookingToTripMap(TripID,bookingID)

Branch(branchID, branchName)

Vehicle(vehicleID,vehicleNumber)

There is a one to one relationship between (Booking,Branch) and (Trip, Vehicle) and Many to many relationship between Booking, Trip which is saved in the table BookingToTripMap.

Now I want to extract a query that would return the following

Booking.RefNumber   Booking.CustomerName    Vehicle.VehicleNumber

(All vehicle numbers in one cell)

Answer 1

Here is your query

SELECT B.RefNumber, B.CustomerName, V.VehicleNumber
FROM ((Booking AS B INNER JOIN BookingToTripMap AS BT
     ON B.bookingID = BT.bookingID) INNER JOIN TRIP as T
     ON T.TripID = BT.TripID) INNER JOIN Vehicle as V
     ON V.vehicleID = T.vehicleID

I would add the field bookingID to the table Trip, it seems that the table BookingToTripMap doesn't add any value to your database.

Also, if your vehicle's numbers are unique, you could change the primary key in the Vehicle table to vehicleNumber, and change the same columns in the Trip table. Thus you could retrieve the vehicleNumber directly from the Trip table.

I'm just guessing in that, based on the given information.

Regards,