Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: use if exists in select?

    I got a database where the language is set to default by a number. The default is english = 1 and if a user selects a different language it tests if the selected product exists in that language. if it doesn't have that language it should show the default language. How do I do this?

    I tried this:

    select productid, productname, price
    from products
    where languageid in (2) or languageid in (1);

    This of course returns both languages on both products.

    Any ideas?

  2. #2
    Join Date
    Nov 2004
    Posts
    4
    Do I need to use exists or not exists in the query to make it work?

    Any idea seems like a good idea now....

  3. #3
    Join Date
    Jun 2004
    Posts
    31
    Assuming you are only selecting one productid at a time, and assuming that productid is unique for a language, and assuming that all non-default languages are > 1, an ugly hack woud be:

    SELECT productid, productname, price
    FROM products
    WHERE productid = 12345
    ORDER BY languageid DESC
    LIMIT 1
    ;

    Of course, substitute the product ID for 12345 in the query.

  4. #4
    Join Date
    Jun 2004
    Posts
    31
    Let me amend that:

    SELECT productid, productname, price
    FROM products
    WHERE productid = 12345
    AND languageid IN (1, 2)
    ORDER BY languageid DESC
    LIMIT 1
    ;

    Of course, substitute the product ID for 12345 in the query and substitute the current languageid for the 2 in the query.

  5. #5
    Join Date
    Nov 2004
    Posts
    4
    Quote Originally Posted by ms139us
    Assuming you are only selecting one productid at a time, and assuming that productid is unique for a language, and assuming that all non-default languages are > 1, an ugly hack woud be:

    SELECT productid, productname, price
    FROM products
    WHERE productid = 12345
    AND languageid IN (1, 2)
    ORDER BY languageid DESC
    LIMIT 1
    ;
    I am selecting more than one product at a time.
    each product have an unique product id.

    I had to change the database to have a new table call productInfo because each product can be in several languages. ProductInfo has ProductID and LanguageID which is a number. 1 is english.

    So I got two tables: Products and ProductInfo.

    I want to query the database to list out the products with the language I specify. If I wants all the products in French I should get a listing with all products in french and then the one that is not in french should come in english.

    I hope this is more understandable. Any ideas now??

  6. #6
    Join Date
    Jun 2004
    Posts
    31
    I think I understand. The table "products" has product listings, keyed by productid, with no description. The table "productinfo" has the product descriptions, keyed by productid and languageid.

    The query must list all of the products and their descriptions in the chosen language, or in English if a description is not in the chosen language. Am I close?

    Given the above, you really have two queries:
    * A query that selects all of the products and descriptions in the chosen language
    * A query that selects all of the products and descriptions in English where the chosen language does not exist

    These can be combined via UNION:

    -- items in chosen language
    SELECT p.productid, i.productname, price
    FROM products p, productinfo i
    WHERE p.productid = 12345
    AND p.productid = i.productid
    AND languageid = 2
    UNION
    -- remaining products in English
    SELECT p.productid, i.productname, price
    FROM products p, productinfo i
    WHERE p.productid = 12345
    AND p.productid = i.productid
    AND languageid = (1)
    AND p.productid NOT IN (
    SELECT productid
    FROM productinfo
    WHERE languageid = 2
    )
    ;

    If you do this, make sure there is an index on productinfo (languageid).

    Hope this helps.

  7. #7
    Join Date
    Oct 2004
    Posts
    2
    Code:
    select
         p.productid
        ,i.productname
        ,p.price
    from
        products p
        inner join
            productinfo i
        on
            p.productid = i.productid
            and
            (
                i.languageid = 2
                or
                (
                    (select count(*) from productinfo i2 where i2.productid = p.productid and i2.languageid = 2) = 0
                    and
                    i.languageid = 1
                )
            )
    ;
    I don't know if logical operations are done like in C/C++. If yes, when a product have the languageid = 2, beeing an OR comparison, it is not needed to execute the second part, since the result is already know (true). In C we can trust that in this case the right part of the expression will not be executed.
    If doesn't have a product with languageid = 2, then it will be necessariy to test the second part of the expression.
    If Postgres execute both, in any case, it is just a performance question, but will work too. (will work? you tell me. :-) )

  8. #8
    Join Date
    Nov 2004
    Posts
    4

    Smile

    Thanks skydiver...your query did the magic.

Posting Permissions

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