SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    join 3 tables that populate pulldown lists (was "join issue")

    I have 3 tables that populate pulldown lists, which are set up as;
    Code:
    menu_state     menu_country     artists
    state_id       country_id       artist_id
    state_name     country_name     artist_name
    I then have a table 'tour_dates' that will store the data selected from the pulldowns, along with other data.

    I'm trying to JOIN these. So far the artist_id and artist_name is displaying correctly but I'm having trouble with the state and country displaying.
    Code:
    SELECT tour_id,artist_name,show_date,venue,city,state_id,zip,country_id,website,add_bands,notes FROM tour_dates,artists WHERE tour_dates.artist_id = artists.artist_id ORDER BY artist_name
    
    SELECT state_name FROM menu_states,tour_dates WHERE menu_states.state_id = tour_dates.state_id
    
    SELECT country_name FROM menu_countries,tour_dates WHERE menu_countries.country_id = tour_dates.country_id
    I've tried joining all 3 into 1 query but just get a huge list of the same record.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tgavin
    I've tried joining all 3 into 1 query but just get a huge list of the same record.
    i don't understand what you are trying to do. are you trying to get one huge query that will give you enough information to fill in all of the drop-down boxes? that's not a good idea. you should instead be writing one query for each drop-down box.

  3. #3
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    i don't understand what you are trying to do. are you trying to get one huge query that will give you enough information to fill in all of the drop-down boxes? that's not a good idea. you should instead be writing one query for each drop-down box.
    Thanks for your reply.

    I'm displaying a list of tour dates for each band in the db. When entering a new tour date into the tour_dates table, you select the artist, state and country from db generated pulldown menus. The common value between tables is the artist_id, state_id and country_id.

    I'm trying to join all together so that when displaying the tour_date in a web page it doesn't display the id number for each, but the "label" instead.

    So, if state_id = 1 and state_name = Alabama, I want 'Alabama' to be displayed instead of '1'.

    Maybe this will help:
    Code:
    CREATE TABLE `tour_dates` (
      `tour_id` mediumint(7) NOT NULL auto_increment,
      `artist_id` smallint(3) NOT NULL default '0',
      `show_date` date NOT NULL default '0000-00-00',
      `venue` varchar(100) NOT NULL default '',
      `city` varchar(30) NOT NULL default '',
      `state_id` varchar(2) NOT NULL default '',
      `zip` varchar(10) default NULL,
      `country_id` varchar(3) NOT NULL default '',
      `website` varchar(60) default NULL,
      `add_bands` varchar(255) default NULL,
      `notes` text,
      `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
      PRIMARY KEY  (`tour_id`)
    ) TYPE=MyISAM
    
    CREATE TABLE `menu_countries` (
      `country_id` smallint(3) NOT NULL auto_increment,
      `country_name` varchar(40) NOT NULL default '',
      PRIMARY KEY  (`country_id`)
    ) TYPE=MyISAM
    
    CREATE TABLE `menu_states` (
      `state_id` smallint(3) NOT NULL auto_increment,
      `state_name` varchar(14) NOT NULL default '',
      PRIMARY KEY  (`state_id`)
    ) TYPE=MyISAM
    
    CREATE TABLE `artists` (
      `artist_id` smallint(5) NOT NULL auto_increment,
      `artist_name` varchar(60) NOT NULL default '',
      `artist_bio` text,
      `artist_website` varchar(60) default NULL,
      `on_tour` enum('0','1') default '0',
      PRIMARY KEY  (`artist_id`)
    ) TYPE=MyISAM

  4. #4
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    got it sorted out
    Code:
    SELECT tour_dates.state_id,state_name,show_date,venue,city,zip,website,add_bands,notes, tour_dates.country_id,country_name,tour_dates.artist_id,artist_name FROM tour_dates LEFT JOIN menu_states ON tour_dates.state_id=menu_states.state_id LEFT JOIN menu_countries ON tour_dates.country_id=menu_countries.country_id LEFT JOIN artists ON tour_dates.artist_id=artists.artist_id ORDER BY artist_name


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •