very tough query
ok, here are the 3 tables i have that are related: mysql desc poll_questions; ++-+--+-++-- --+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++-- --+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++-- --+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ I'm pretty sure this is completely normalized. I have no problem joining the 3 tables to do SELECTS...but what happens when I want to delete a poll? I can easily elete from the poll_questions and poll_answers tables, but how do I delete the necessary rows in the poll_votes table? I tried doing a DELETE query just like a SELECT, but it didn't work: DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); Please help! Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: very tough query
Hi Darren, On Tue, 5 Mar 2002 09:42:50 -0800 Daren Cotter [EMAIL PROTECTED] wrote: mysql desc poll_questions; ++-+--+-++- ---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++- ---+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++- ---+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); I haven't tried it yet, but maybe you can try: DELETE poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, poll_votes WHERE (poll_questions.poll_id = poll_answers.poll_id) AND (poll_answers.answer_id = poll_votes.answer_id) AND (poll_questions.poll_id = 1); --bk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: very tough query
Thanks much for the try, but I get an error: mysql DELETE - poll_questions, poll_answers, poll_votes - FROM - poll_questions, poll_answers, poll_votes - WHERE - (poll_questions.poll_id = poll_answers.poll_id) - AND (poll_answers.answer_id = poll_votes.answer_id) - AND (poll_questions.poll_id = 1); ERROR 1064: You have an error in your SQL syntax near 'poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, ' at line 2 -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 8:27 AM To: [EMAIL PROTECTED] Cc: Daren Cotter Subject: Re: very tough query Hi Darren, On Tue, 5 Mar 2002 09:42:50 -0800 Daren Cotter [EMAIL PROTECTED] wrote: mysql desc poll_questions; ++-+--+-++- ---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++- ---+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++- ---+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); I haven't tried it yet, but maybe you can try: DELETE poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, poll_votes WHERE (poll_questions.poll_id = poll_answers.poll_id) AND (poll_answers.answer_id = poll_votes.answer_id) AND (poll_questions.poll_id = 1); --bk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: very tough query
From: Daren Cotter [EMAIL PROTECTED] ...what happens when I want to delete a poll? Do it machanically. That is, do a query in advance to find the data that needs to be deleted, then go through and delete it. The logic for joining a delete can be written, but MySQL doesn't support it. (Hey folks, does anything else support joined deletes?) By the way, since poll_votes has a PK of aim_screenname/answer_id, folks can give each answer for a given poll once. As apposed to just one answer per poll. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: very tough query
Well after reviewing the DELETE section of the manual, I see nothing to suggest that it supports multiple-table deletes... # Nathan - Original Message - From: Daren Cotter [EMAIL PROTECTED] To: Nathan [EMAIL PROTECTED]; Daren Cotter [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 12:37 PM Subject: RE: very tough query Here's what happens: ERROR 1064: You have an error in your SQL syntax near ' poll_answers, poll_votes where (poll_questions.poll_id = poll_answers.poll_id) ' at line 1 -Original Message- From: Nathan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 9:28 AM To: Daren Cotter Subject: Re: very tough query I haven't read this thread but I thought you couldn't have anything besides a priority between DELETE and FROM... What happens with: mysql DELETE FROM - poll_questions, poll_answers, poll_votes - WHERE - (poll_questions.poll_id = poll_answers.poll_id) - AND (poll_answers.answer_id = poll_votes.answer_id) - AND (poll_questions.poll_id = 1); # Nathan - Original Message - From: Daren Cotter [EMAIL PROTECTED] To: Batara Kesuma [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: Daren Cotter [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 11:42 AM Subject: RE: very tough query Thanks much for the try, but I get an error: mysql DELETE - poll_questions, poll_answers, poll_votes - FROM - poll_questions, poll_answers, poll_votes - WHERE - (poll_questions.poll_id = poll_answers.poll_id) - AND (poll_answers.answer_id = poll_votes.answer_id) - AND (poll_questions.poll_id = 1); ERROR 1064: You have an error in your SQL syntax near 'poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, ' at line 2 -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 8:27 AM To: [EMAIL PROTECTED] Cc: Daren Cotter Subject: Re: very tough query Hi Darren, On Tue, 5 Mar 2002 09:42:50 -0800 Daren Cotter [EMAIL PROTECTED] wrote: mysql desc poll_questions; ++-+--+-++- ---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++- ---+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++- ---+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); I haven't tried it yet, but maybe you can try: DELETE poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, poll_votes WHERE (poll_questions.poll_id = poll_answers.poll_id) AND (poll_answers.answer_id = poll_votes.answer_id) AND (poll_questions.poll_id = 1); --bk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
