Help with a query

Dev Shed Forums Sponsor:

November 11th, 2012, 04:21 AM

JRowe95

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 18 m 19 sec
Reputation Power: 0

Help with a query

Below is the SQL code for a database.
I am having trouble doing a search query.
I need a query that will display the first name, last name and id of all the accounts that have tickets to the event with the id of `1`.
I know I need to use JOIN or something like that but I just cant understand how I can get it to work. Please help. Thanks in advance.

Code:

CREATE TABLE IF NOT EXISTS `accounts` ( `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID', `username` varchar(32) NOT NULL COMMENT 'Username', `password` char(128) NOT NULL COMMENT 'Password', `first_name` varchar(64) NOT NULL COMMENT 'First Name', `last_name` varchar(64) NOT NULL COMMENT 'Last Name', `dob` date NOT NULL COMMENT 'Date of Birth', `access` int(1) NOT NULL DEFAULT '0' COMMENT '0 = User, 1 = Admin', PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; INSERT INTO `accounts` (`id`, `username`, `password`, `first_name`, `last_name`, `dob`, `access`) VALUES (1, 'user1', 'b14361404c078ffd549c03db443c3fede2f3e534d73f78f77301ed97d4a436a9fd9db05ee8b325c0ad36438b43fec8510c2  04fc1c1edb21d0941c00e9e2c1ce2', 'John', 'Smith', '2012-11-11', 0); CREATE TABLE IF NOT EXISTS `acts` ( `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(255) NOT NULL COMMENT 'Name', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; INSERT INTO `acts` (`id`, `name`) VALUES (1, 'Some Band'), (2, 'Some Other Band'); CREATE TABLE IF NOT EXISTS `events` ( `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(255) NOT NULL COMMENT 'Name', `date` date NOT NULL COMMENT 'Date', `main_act` int(10) NOT NULL COMMENT 'Main Act', PRIMARY KEY (`id`), KEY `main_act` (`main_act`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; INSERT INTO `events` (`id`, `name`, `date`, `main_act`) VALUES (1, 'The Best Music Show', '2013-01-01', 1); CREATE TABLE IF NOT EXISTS `support_acts` ( `event_id` int(10) NOT NULL COMMENT 'Event ID', `act_id` int(10) NOT NULL COMMENT 'Act ID', KEY `event_id` (`event_id`,`act_id`), KEY `act_id` (`act_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `support_acts` (`event_id`, `act_id`) VALUES (1, 2); CREATE TABLE IF NOT EXISTS `tickets` ( `ticket_id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'Ticket ID', `account_id` int(10) NOT NULL COMMENT 'Account ID', `event_id` int(10) NOT NULL COMMENT 'Event ID', PRIMARY KEY (`ticket_id`), KEY `account_id` (`account_id`,`event_id`), KEY `event_id` (`event_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `tickets` (`ticket_id`, `account_id`, `event_id`) VALUES (1, 1, 1); ALTER TABLE `events` ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`main_act`) REFERENCES `acts` (`id`) ON UPDATE CASCADE; ALTER TABLE `support_acts` ADD CONSTRAINT `support_acts_ibfk_1` FOREIGN KEY (`act_id`) REFERENCES `acts` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `support_acts_ibfk_2` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON UPDATE CASCADE; ALTER TABLE `tickets` ADD CONSTRAINT `tickets_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `tickets_ibfk_2` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON UPDATE CASCADE;