Hi, guys could you help me write a join query!
Posted 27 February 2006 - 08:26 PM
I need a basic query written for me, I can write it as a nested query but my version of mysql doesnt support it,
Ive got 2 tables , the names and fields are below
tables ( table_id, seats, smoking)
table_id is auto-increment , seats hold a whole number , smoking holds yes or no
newbooking(booking_id, table_id, username, date, time, period);
booking_id is the primary key autoincrement
table_id is the foreign key from the tables table
username is varchar
date holds the date as a unix time stamp
time holds the time as a timestamp
period holds a string either lunch or dinner
Query !! Im so sorry for the bother !
I need to return all of fields within the tables table, where table.table_id is not equal to the newbooking.table.id for a given date and period.
so basically all of the tables from the tables table, which are not present within the booking table for a given date and period,.
I dunno guys is the above clear, can anyone help me sort this out, sorry for the bother !
Thanks for your help
Posted 27 February 2006 - 09:05 PM
Why don't you post the nested query that you have written already?
thank you for your message, the nested query i think would be something along the lines
SELECT * FROM tables WHERE table_id /=
(SELECT table_id FROM newbooking where date = $date && period = $perid)
$date holds the specified date
period holds the specified period
Maybe the not equals should be not like as the query could return more then one row!
Posted 27 February 2006 - 09:39 PM
SELECT t.table_id FROM tables AS t LEFT JOIN newbooking AS n ON t.table_id = n.table_id WHERE n.table_id IS NULL OR ( n.date <> $date AND n.period <> $period )
Hope that helps.
Posted 27 February 2006 - 10:25 PM
I tryed that query it gave me a syntax error any ideas
Posted 27 February 2006 - 10:46 PM
Posted 27 February 2006 - 10:56 PM
...which syntax error? Was it a PHP error? MySQL? Did it give you any more information?
No it didnt im sorry ,
Posted 28 February 2006 - 12:42 AM
Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in c:\project\htdocs\hotel\customer\book.php on line 82
Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in c:\project\htdocs\hotel\customer\book.php on line 82
Im just lost, do you think it could be something to do with the fact
that table_id is a primary key auto increment
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users