SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)

    [MySQL] use value B if value A not present.....

    Hi all

    Cant get my head around this one and I think it must be tiredness or (legal) drugs....

    I have a table that has:

    Code:
    id (int) PK AI
    menu_text VARCHAR(255)
    language VARCHAR(5)
    which holds data such as:

    Code:
    1 | home       | en
    2 | about       | en
    3 | contact    | en
    4 | home       | de
    5 | about       | de
    What I am trying to achieve is to select menu_text based on language which I can do BUT if the row is not present - in this case contact | de, use the contact | en instead.

    So it behaves like a default value
    Pseudo code would be something like

    Code:
    select
          id
        , home
    from
         mytable
    where
         language = 'de'
    if exists else 
         language = 'en'

    I could write it outside of MySQL if needs be but wondered if there was a way to do it natively in MySQL

    Cheers

    Spike
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  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)
    Code:
    SELECT COALESCE(de.menu_text,en.menu_text) AS menu_text
      FROM daTable AS en
    LEFT OUTER
      JOIN daTable AS de
        ON de.menu_text = en.menu_text
       AND de.language = 'de'
     WHERE en.language = 'en'
    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
  •