[SOLVED] help with a query for multiple table delete

[marksie1988]

ok i have 3 tables

 

tickets

ticket_questions

ticket_responses

 

the table tickets and ticket_questions both store the ticket_id field but the ticket_responses only stores the ticket_questions.question_id

 

i need to delete a ticket but when i delete a ticket i need it to delete the questions and the responses here is the query i have created i can do a select but when i change it to delete i cant get it to work i think i need to do a JOIN but have no idea how to do this i have looked at mysql website for help but just got confused help and an explanation of it would be ace please here is the query i made

 

DELETE FROM tickets, ticket_questions, ticket_responses WHERE tickets.ticket_id = '1' && ticket_questions.ticket_id = '1' && ticket_questions.question_id=ticket_responses.question_id

[Barand]

try

DELETE FROM tickets, ticket_questions, ticket_responses 
WHERE tickets.ticket_id = '1' 
AND ticket_questions.ticket_id = tickets.ticket_id 
AND ticket_questions.question_id=ticket_responses.question_id

[marksie1988]

no i tried that query but no joy says theres error with the syntax

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE tickets.ticket_id = '1'

AND ticket_questions.ticket_id = tickets.ticket_' at line 2

[marksie1988]

here are my tables if this helps

tickets:

CREATE TABLE IF NOT EXISTS `tickets` (
  `ticket_id` mediumint(9) NOT NULL auto_increment,
  `ticket_ref` varchar(20) NOT NULL,
  `category` varchar(30) NOT NULL,
  `description` varchar(400) NOT NULL,
  `priority` varchar(10) NOT NULL,
  `instruction` varchar(500) NOT NULL,
  `user_id` int(10) NOT NULL,
  `ticket_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `status` varchar(50) NOT NULL,
  `allocated_user_id` mediumint(9) default NULL,
  PRIMARY KEY  (`ticket_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=58 ;

 

Ticket_questions:

CREATE TABLE IF NOT EXISTS `ticket_questions` (
  `question_id` mediumint(9) NOT NULL auto_increment,
  `ticket_id` mediumint(9) NOT NULL,
  `question_text` varchar(1000) NOT NULL,
  `question_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `question_responded` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`question_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ;

 

Ticket_responses:

CREATE TABLE IF NOT EXISTS `ticket_responses` (
  `response_id` mediumint(9) NOT NULL auto_increment,
  `question_id` mediumint(9) NOT NULL,
  `response_text` varchar(1000) NOT NULL,
  `response_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`response_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=65 ;

 

i cant get a query to work that can delete the entries unless i need to run 2 queries in order for this to work, Thanks Steve

[Barand]

Sorry. Should be

 

DELETE tickets, ticket_questions, ticket_responses
FROM tickets, ticket_questions, ticket_responses
WHERE tickets.ticket_id = '1'
    AND ticket_questions.ticket_id = tickets.ticket_id
    AND ticket_questions.question_id=ticket_responses.question_id

[marksie1988]

nice one thanks that works now