Professional Web Applications Themes

Using default setting in SQL query - MySQL

Dear group. I have a problem i cannot solve myself, will appreciate any help / link / direction possible. In need to add localization support and I am looking for some way to use default setting in SQL query, see in example below: ------------------------------------------------------- -- Tables -- (MySQL format) ------------------------------------------------------- CREATE TABLE Books ( ID INT(4) NOT NULL AUTO_INCREMENT, Title TEXT NOT NULL, Author TEXT NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Descriptions ( ID INT(4) NOT NULL, Language VARCHAR(2) NOT NULL, Description TEXT NOT NULL, PRIMARY KEY (ID, Language) ); Now i assume English being default language. For ...

  1. #1

    Default Using default setting in SQL query

    Dear group.

    I have a problem i cannot solve myself, will appreciate any help / link
    / direction possible.

    In need to add localization support and I am looking for some way to
    use default setting in SQL query, see in example below:

    -------------------------------------------------------
    -- Tables
    -- (MySQL format)
    -------------------------------------------------------
    CREATE TABLE Books
    (
    ID INT(4) NOT NULL AUTO_INCREMENT,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    PRIMARY KEY (ID)
    );

    CREATE TABLE Descriptions
    (
    ID INT(4) NOT NULL,
    Language VARCHAR(2) NOT NULL,
    Description TEXT NOT NULL,
    PRIMARY KEY (ID, Language)
    );

    Now i assume English being default language. For example i want to
    choose description for book with ID = 1001 AND Language = 'JP', but if
    there is no such row, i want to select a row with default language (ID
    = 1001 AND Language = 'EN').

    The question is if its possible with a single SQL query, without
    additional logic.

    Thanks for your time.

    Guiding5 Guest

  2. #2

    Default Re: Using default setting in SQL query

    Guiding5 wrote:
    > Now i assume English being default language. For example i want to
    > choose description for book with ID = 1001 AND Language = 'JP', but if
    > there is no such row, i want to select a row with default language (ID
    > = 1001 AND Language = 'EN').
    >
    > The question is if its possible with a single SQL query, without
    > additional logic.
    SELECT B.*, COALESCE(D_LOC.Description, D_DFLT.Description, 'No
    description available') AS Description
    FROM Books AS B LEFT OUTER JOIN Descriptions AS D_LOC
    ON B.ID = D_LOC.ID AND D_LOC.Language = 'JP'
    LEFT OUTER JOIN Descriptions AS D_DFLT
    ON B.ID = D_LOC.ID AND D_LOC.Language = 'EN'
    WHERE B.ID = 1001;

    (But don't let Celko see you using "ID" as a field name. ;-)

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Using default setting in SQL query

    Bill Karwin wrote:
    > LEFT OUTER JOIN Descriptions AS D_DFLT
    > ON B.ID = D_LOC.ID AND D_LOC.Language = 'EN'
    Yikes! I apologize, but I made a copy & paste mistake.

    These lines should be:

    LEFT OUTER JOIN Descriptions AS D_DFLT
    ON B.ID = D_DFLT.ID AND D_DFLT.Language = 'EN'


    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Re: Using default setting in SQL query

    I loved the solution, tho it shows "No description available" string
    instead of EN one.

    Here comes my test case:
    -------------------------------------------------------
    -- Tables
    -- (MySQL format)
    -------------------------------------------------------
    CREATE TABLE Books
    (
    ID INT(4) NOT NULL AUTO_INCREMENT,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    PRIMARY KEY (ID)
    );

    CREATE TABLE Descriptions
    (
    ID INT(4) NOT NULL,
    Language VARCHAR(2) NOT NULL,
    Description TEXT NOT NULL,
    PRIMARY KEY (ID, Language)
    );

    -------------------------------------------------------
    -- Sample data
    -- (MySQL format)
    -------------------------------------------------------

    INSERT INTO Books VALUES(1, 'Dune', 'Frank Herbert');
    INSERT INTO Books VALUES(2, 'Dune Messiah', 'Frank Herbert');
    INSERT INTO Books VALUES(3, 'Heretics of Dune', 'Frank Herbert');
    INSERT INTO Books VALUES(4, 'Children of Dune', 'Frank Herbert');

    INSERT INTO Descriptions VALUES(1, 'EN', 'Desc 1 EN');
    INSERT INTO Descriptions VALUES(1, 'JP', 'Desc 1 JP');
    INSERT INTO Descriptions VALUES(1, 'FR', 'Desc 1 FR');

    INSERT INTO Descriptions VALUES(2, 'EN', 'Desc 2 EN');
    INSERT INTO Descriptions VALUES(2, 'JP', 'Desc 2 JP');
    INSERT INTO Descriptions VALUES(2, 'FR', 'Desc 2 FR');

    INSERT INTO Descriptions VALUES(3, 'EN', 'Desc 3 EN');
    INSERT INTO Descriptions VALUES(3, 'FR', 'Desc 3 FR');

    INSERT INTO Descriptions VALUES(4, 'EN', 'Desc 4 EN');
    INSERT INTO Descriptions VALUES(4, 'JP', 'Desc 4 JP');

    SELECT B.*, COALESCE(D_LOC.Description, D_DFLT.Description, 'No
    description available') AS Description
    FROM Books AS B LEFT OUTER JOIN Descriptions AS D_LOC
    ON B.ID = D_LOC.ID AND D_LOC.Language = 'JP'
    LEFT OUTER JOIN Descriptions AS D_DFLT
    ON B.ID = D_LOC.ID AND D_LOC.Language = 'EN'

    --------------------------------
    -- Query results:
    --------------------------------

    ID, Title, Author, Description
    1, 'Dune', 'Frank Herbert', 'Desc 1 JP'
    2, 'Dune Messiah', 'Frank Herbert', 'Desc 2 JP'
    3, 'Heretics of Dune', 'Frank Herbert', 'No description available'
    4, 'Children of Dune', 'Frank Herbert', 'Desc 4 JP'

    As you see when ID=3, it falls to default string ..

    Bill Karwin wrote:
    > Guiding5 wrote:
    > > Now i assume English being default language. For example i want to
    > > choose description for book with ID = 1001 AND Language = 'JP', but if
    > > there is no such row, i want to select a row with default language (ID
    > > = 1001 AND Language = 'EN').
    > >
    > > The question is if its possible with a single SQL query, without
    > > additional logic.
    >
    > SELECT B.*, COALESCE(D_LOC.Description, D_DFLT.Description, 'No
    > description available') AS Description
    > FROM Books AS B LEFT OUTER JOIN Descriptions AS D_LOC
    > ON B.ID = D_LOC.ID AND D_LOC.Language = 'JP'
    > LEFT OUTER JOIN Descriptions AS D_DFLT
    > ON B.ID = D_LOC.ID AND D_LOC.Language = 'EN'
    > WHERE B.ID = 1001;
    >
    > (But don't let Celko see you using "ID" as a field name. ;-)
    This time its backwards compatability :)
    >
    > Regards,
    > Bill K.
    Thanks alot !

    Guiding5 Guest

  5. #5

    Default Re: Using default setting in SQL query

    Now its perfect, thanks A LOT ! :)

    Bill Karwin wrote:
    > Bill Karwin wrote:
    > > LEFT OUTER JOIN Descriptions AS D_DFLT
    > > ON B.ID = D_LOC.ID AND D_LOC.Language = 'EN'
    >
    > Yikes! I apologize, but I made a copy & paste mistake.
    >
    > These lines should be:
    >
    > LEFT OUTER JOIN Descriptions AS D_DFLT
    > ON B.ID = D_DFLT.ID AND D_DFLT.Language = 'EN'
    >
    >
    > Regards,
    > Bill K.
    Guiding5 Guest

Similar Threads

  1. Need help setting a default variable
    By Married2theMachine in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: August 18th, 07:49 PM
  2. Replies: 4
    Last Post: April 9th, 05:53 PM
  3. Setting a default scanner...??
    By ZR in forum Adobe Photoshop Elements
    Replies: 0
    Last Post: August 26th, 01:34 PM
  4. Setting the default button
    By Sean in forum ASP.NET General
    Replies: 2
    Last Post: July 31st, 08:53 PM
  5. Replies: 3
    Last Post: July 24th, 07:19 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139