Hi, guys could you help me write a join query!

[norman100]

Hi there guys

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

1)

tables ( table_id, seats, smoking)

table_id is auto-increment , seats hold a whole number , smoking holds yes or no


2)

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

N

[fenway]

Why don't you post the nested query that you have written already?

[norman100]

[!--quoteo(post=349971:date=Feb 27 2006, 03:54 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 27 2006, 03:54 PM) [snapback]349971[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Why don't you post the nested query that you have written already?
[/quote]


Hi there

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!

Thanks guys

[fenway]

Try the following (UNTESTED):

[code]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 )[/code]

Hope that helps.

[norman100]

Hi there

I tryed that query it gave me a syntax error any ideas

[wickning1]

...which syntax error? Was it a PHP error? MySQL? Did it give you any more information?

[norman100]

[!--quoteo(post=350031:date=Feb 27 2006, 05:46 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 27 2006, 05:46 PM) [snapback]350031[/snapback][/div][div class=\'quotemain\'][!--quotec--]
...which syntax error? Was it a PHP error? MySQL? Did it give you any more information?
[/quote]

No it didnt im sorry ,

[fenway]

I'm sure that it can -- make sure you use mysql_error() to trap them.

[norman100]

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in c:\project\htdocs\hotel\customer\book.php on line 80

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

thanks

[fenway]

Sounds like the DB connection failed... hence the query didn't work either.