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?

Do I need to use exists or not exists in the query to make it work?

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

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.

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.

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??

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.

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. :-) )

Thanks skydiver...your query did the magic.