SQL query losing results after adding a left join

I am trying to join three tables together but having a problem getting the correct results.

The query below does not show all of my desired results - only the ones where there is an associated flight. So the first problem is how to also include results without a flight.

It night help to know that web_bookings has a column named '开发者_如何学运维flights' which is either a Y or N.

SELECT wb.booking_ref 
FROM web_bookings wb 
LEFT JOIN prod_info pi ON wb.location = pi.location 
LEFT JOIN flight_prices fp ON (wb.inc_flights = 'Y' AND fp.dest_date = (pi.dest_airport + ' ' + wb.sort_date)) 
WHERE fp.dest_cheapest = 'Y' AND wb.customer = '12345'

Thanks

it's the where clause, the fp.dest_cheapest = 'Y' is causing a narrowing effect (y <> null)

to get around that put the fp clause into the left join as such

SELECT wb.booking_ref 
FROM web_bookings wb 
LEFT JOIN prod_info pi ON wb.location = pi.location 
LEFT JOIN flight_prices fp ON (wb.inc_flights = 'Y' AND fp.dest_date = (pi.dest_airport +  ' ' + wb.sort_date)) 
and fp.dest_cheapest = 'Y' 

WHERE wb.customer = '12345'

as for speed, check your indexes (make sure that location, inc_flights are indexed) and see how that works.

Not sure what db you are using, but you ought to be able to do an explain plan (or what not) that will show you the bottleneck spots so that you may increase the speed.

You are essentially 'undoing' the left-join (making it an inner join) with your fp.dest_cheapest where clause.

try writing three single select queries and use 'UNION' to join them can also help and make things easier

http://www.w3schools.com/sql/default.asp