#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep 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', 'b14361404c078ffd549c03db443c3fede2f3e534d73f78f77301ed97d4a436a9fd9db05ee8b325c0ad36438b43fec8510c204fc1c1edb21d0941c00e9e2c1ce2', '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;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Originally Posted by JRowe95
    I know I need to use JOIN or something like that
    correct, you do

    where did you get that script to create the tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    correct, you do

    where did you get that script to create the tables?
    I made it myself in phpMyAdmin and just exported it as an SQL file.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    76
    Rep Power
    3
    should be something like this:
    SELECT accounts.id, accounts.first_name, account.last_name from accounts JOIN tickets ON accounts.id=tickets.account_id WHERE tickets.event_id=1

IMN logo majestic logo threadwatch logo seochat tools logo