1

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)

criticalfix
  • 2,870
  • 1
  • 19
  • 32
nikhilthecoder
  • 301
  • 1
  • 3
  • 12
  • 1
    See at http://stackoverflow.com/questions/12559551/sql-server-equivalent-of-wm-concat-function – li-on May 29 '13 at 09:40
  • 2
    @nikhilthecoder: How is the Booking table related to Vehicle table. I did not see any relationship or foregien key in any table – Deepak.Aggrawal May 29 '13 at 09:41
  • Hi deepak, sorry, my mistake, it was bookingID in the Table BookingToTripMap, not vehilceID. – nikhilthecoder May 29 '13 at 09:44
  • Have you tried something? Post your code – Yaroslav May 29 '13 at 09:52
  • Best if you can also show the entity diagram or relationship information (like many-to-many, one-to-many). – Elmer May 29 '13 at 10:30
  • Have added the rlationship mapping to the question – nikhilthecoder May 29 '13 at 10:42
  • I think you're stuck with [FOR XML PATH](http://msdn.microsoft.com/en-us/library/ms189885.aspx) ([examples here](http://msdn.microsoft.com/en-us/library/bb510462.aspx)) as discussed in http://stackoverflow.com/questions/1621747/concat-field-value-to-string-in-sql-server – criticalfix May 29 '13 at 14:22
  • FWIW, here's some joining, but I gather you've got that part. SELECT b.RefNumber, b.CustomerName, v.VehicleNumber FROM Booking b JOIN BookingToTripMap bt ON bt.BookingID = b.BookingID JOIN Trip t ON t.TripID = bt.TripID LEFT JOIN Vehicle v ON v.VehicleID = t.VehicleID LEFT JOIN Branch br ON br.BranchID = b.BranchID – criticalfix May 29 '13 at 14:23

1 Answers1

0

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,

Clon
  • 1,035
  • 8
  • 12
  • I assume that the relations between (Branch, Booking) and (Trip, Vehicle) are not one to one, but one to many. Once you fix a Booking, it only corresponds to a Branch, and once you fix a Trip, maybe it has only a correspondent vehicle... But a vehicle makes many trips and the branch many bookings, right? – Clon Jul 07 '13 at 20:39