Jump to content

Archived

This topic is now archived and is closed to further replies.

norman100

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
Why don't you post the nested query that you have written already?

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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 ,

Share this post


Link to post
Share on other sites
I'm sure that it can -- make sure you use mysql_error() to trap them.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Sounds like the DB connection failed... hence the query didn't work either.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.