Multiple distinct and NOT EXIST problem

[metrostars]

This is my query currently:

 

"SELECT distinct(departicao), depart FROM flights WHERE NOT EXISTS(SELECT `bookings`.`id` FROM bookings WHERE `date`='$date' AND `bookings`.`flightid`=`flights`.`id`) AND `$day`='1' GROUP BY departicao"

 

And say i have these 2 tables.

 

[pre]

bookings:

          id      flightid  date

          0        2        2007-12-30

 

flights:

id        depart            departicao  Mon      Tue    Wed      Thu      Fri      Sat      Sun        deptime

2        Newcastle          EGNT      0          0      0        0        0        0        1        00:14:56

4        Los Angeles        KLAX      0          0      0        0        0        0        0        15:00:00

6        Los Angeles        KLAX      0          0      0        0        0        0        1        11:30:00 

[/pre]

 

However, only Newcastle shows in this case, only Los Angeles should show.

But id i empty the bookings table, both Newcastle and Los Angeles show, as they should.

[drranch]

What version of MYSQL are you using?

[metrostars]

It's under my username.

[drranch]

Are those all the columns in your bookings table?

 

I see in your syntax you have in the sub query "SELECT 'bookings', ......

 

If you don't have a colum called bookings in your table then your script should be catching that saying that there isn't a recognized column. 

 

"SELECT distinct(departicao), depart FROM flights WHERE NOT EXISTS(Select ID FROM Bookings WHERE date = $date AND bookings.flightid=flights.id) AND $day=1 GROUP BY departicao"

 

Just a guess I'm no expert, but learning along with everyone else.

 

 

[Barand]

I see in your syntax you have in the sub query "SELECT 'bookings', ......

 

No, That's "SELECT bookings.id..." meaning "select the id from the bookings table"

[Barand]

can we assume that $date=='2007-12-30' and $day=='Sun' ?

 

try

<?php
$date = '2007-12-30';
$day  = date ('D', strtotime($date));

$sql = "SELECT DISTINCT f.departicao, f.depart 
        FROM flights f
            LEFT JOIN bookings b ON  f.id = b.flightid AND f.date = '$date'
        WHERE f.$day = 1
            AND b.flightid IS NULL";
?>