Jump to content


Photo

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


  • Please log in to reply
9 replies to this topic

#1 norman100

norman100
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 27 February 2006 - 08:26 PM

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 February 2006 - 08:54 PM

Why don't you post the nested query that you have written already?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 norman100

norman100
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 27 February 2006 - 09:05 PM

[!--quoteo(post=349971:date=Feb 27 2006, 03:54 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 27 2006, 03:54 PM) View Post[/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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 February 2006 - 09:39 PM

Try the following (UNTESTED):

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 norman100

norman100
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 27 February 2006 - 10:25 PM

Hi there

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

#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 27 February 2006 - 10:46 PM

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

#7 norman100

norman100
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 27 February 2006 - 10:56 PM

[!--quoteo(post=350031:date=Feb 27 2006, 05:46 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 27 2006, 05:46 PM) View Post[/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 ,

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 February 2006 - 11:27 PM

I'm sure that it can -- make sure you use mysql_error() to trap them.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 norman100

norman100
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 28 February 2006 - 12:42 AM

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

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 February 2006 - 03:08 AM

Sounds like the DB connection failed... hence the query didn't work either.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users