SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Distinct output

  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Distinct output

    For a multilingual website for a restaurant I need some kind of DISTINCT output if you can call it that and for the dishes I use the following two tables:
    Code:
    CREATE TABLE IF NOT EXISTS `menu_kaart` (
      `menu_kaart_id` tinyint(2) NOT NULL AUTO_INCREMENT,
      `menu_kaart_type` varchar(32) NOT NULL,
      PRIMARY KEY (`menu_kaart_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    
    CREATE TABLE IF NOT EXISTS `menu_kaart_items` (
      `menu_kaart_item_id` tinyint(2) NOT NULL AUTO_INCREMENT,
      `menu_kaart_id` tinyint(4) NOT NULL,
      `language_abbr` char(2) NOT NULL,
      `menu_item` varchar(32) DEFAULT NULL,
      `menu_item_description` text,
      `menu_item_price` varchar(40) DEFAULT NULL,
      PRIMARY KEY (`menu_kaart_item_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    There are 4 languages so the rows in menu_kaart_items(the second table) are like this:
    (1, 1, 'nl', 'Gestoomde Jacobsschelp', 'Gestoomde sint jacobsschelp met knoflook ', '5.00'),
    (2, 1, 'en', 'Steamed Scallop', 'Steamed scallop with garlic ', '5.00'),
    (3, 1, 'de', 'Gedämpfte Jakobsmuschel', 'Gedämpfte Jakobsmuscheln mit Knoblauch', '5.00'),
    (4, 1, 'fr', 'Pétoncles cuits à la vapeur', 'Pétoncles cuits à la vapeur avec de l'ail', '5.00'),
    In the CMS in need to output the menu_item from just one language but the menu_item_descriptions from all 4! e.a.
    Gestoomde Jacobsschelp
    • Gestoomde sint jacobsschelp met knoflook
    • Steamed scallop with garlic
    • Gedämpfte Jakobsmuscheln mit Knoblauch
    • Pétoncles cuits à la vapeur avec de l'ai
    Can this be done in a query or should this be done in my server side scripting?

    Thank you in advance!
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by donboe View Post
    In the CMS in need to output the menu_item from just one language
    which one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudi. Thanks for the reply. I want to output the one with language_abbr nl.
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT menu_kaart.menu_kaart_id
         , menu_kaart.menu_kaart_type
         , dutch.menu_item
         , menu_kaart_items.menu_item_description
         , menu_kaart_items.menu_item_price
      FROM menu_kaart
    INNER
      JOIN menu_kaart_items AS dutch
        ON dutch.menu_kaart_id = menu_kaart.menu_kaart_id
       AND dutch.language_abbr = 'nl'
    INNER
      JOIN menu_kaart_items
        ON menu_kaart_items.menu_kaart_id = menu_kaart.menu_kaart_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •