SELECT * FROM Bookings WHERE Booking_Skey = 1

SELECT * FROM PitchValues WHERE PitchType_Skey IN ( SELECT PitchType_Skey FROM Bookings WHERE Booking_Skey = 1 )

SELECT	Bookings.Booking_Skey, DATEDIFF(DAY, Bookings.ArrivalDate, Bookings.DepartureDate) * PitchValues.Value AS BookingValue, PitchValues.PitchType_Skey
FROM	Bookings
INNER JOIN PitchValues ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey
	AND Bookings.ArrivalDate BETWEEN PitchValues.StartDate AND PitchValues.EndDate --==========You probably need to add this condition
WHERE (Bookings.Booking_Skey = 1)