0

These are my tables:

Product:
ID PRICE NAME    DESCRIPTION
1  100   laptop  laptop_desc
2  200   mouse   mouse_desc

Product_Translations:
PID  LANG NAME DESCRIPTION
1    ch   伊吾  伊吾伊吾

Please don't worry about name and description in the Product table. We are keeping it to avoid join if default language is selected by the user.

Now I need to write a query to get all the products from Product according to the users's language with fallback that if no translations for name and description are found in Product_Translations get them from Product table. I tried couple of different ways, but couldn't make it work. Update:

I require all the columns from Product table(In this example, I only gave 2, but my actual table has more columns). And one more restriction is that I need to generate this query using JPA Criteria API, so any SQL keywords not supported by JPA may not work for me.

Thanks for your help.

geekprogrammer
  • 1,108
  • 1
  • 13
  • 39

3 Answers3

0

Use LEFT OUTER JOIN:

SELECT 
      p.id
     ,p.price
     ,ISNULL(t.name, p.name) AS translation
     ,ISNULL(t.description, p.description) AS description
FROM Product p
LEFT JOIN Translations t
   ON p.id = t.pid
   AND t.lang = ?

This will work on SQL Server if you use other DB change to COALESCE().

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • coalesce() also works on sql server, so why ever use anything else? – Joel Coehoorn Aug 28 '15 at 04:16
  • 1
    COALESCE is syntactic sugar to CASE in some cases is non-deterministic + it evaluates its type based on type precedence of all expressions not first expression. Only power it has it is part of ANSI standard and supports multiple inputs. – Lukasz Szozda Aug 28 '15 at 04:17
  • Your query is not giving default values, I think the last line should be "AND t.lang=?". One more thing is I require all the columns from Product table. – geekprogrammer Aug 28 '15 at 04:38
  • @geekprogrammer For all column from Product table add simply p.*, but I would rather specify them explicitly, because * in SELECT is bad practice. – Lukasz Szozda Aug 28 '15 at 04:45
  • If I add p.*, it duplicates name and description. In short I need name and description to come only once including the remaining columns. Any way to do ? – geekprogrammer Aug 28 '15 at 04:55
  • @geekprogrammer If you use * you tell DB give me everything you got for that alias, [Why is SELECT * considered harmful?](http://stackoverflow.com/a/3639964/5070879). Why is it so hard to type this additional 10 columns names?? – Lukasz Szozda Aug 28 '15 at 05:00
  • @lad2025 As I said before, I require all the columns in every circumstance, so I'm passing *. So let me know if I can achieve my requirement even with giving * ? – geekprogrammer Aug 28 '15 at 05:37
  • @geekprogrammer No, you basically want `SELECT * EXCLUDE some columns`, there are ways to achieve this but very dirty tricks. Ask other people who answer you, maybe then can help you. – Lukasz Szozda Aug 28 '15 at 05:52
  • @geekprogrammer Or even bettter start new question `I want to SELECT * but without specific column names`, because for this question you get your answer. Accept it or not. – Lukasz Szozda Aug 28 '15 at 05:57
  • If you can live with all columns + two translated ones, then you can use `select *, coalesce(...) as name_translated, coalesce(...) as description_translated from ...` - bad practice, but should work. Replacing some columns in `select *` is directly not possible. – Arvo Aug 28 '15 at 08:49
0
SELECT p.ID, p.Price,
      COALESCE(pt.Name, p.Name) Name, 
      COALESCE(pt.Description, p.Description) Description
FROM Product p
INNER JOIN User u on u.ID = @MyUserID
LEFT JOIN Product_Translations pt ON pt.PID = p.ID AND pt.LANG = u.LANG
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Thanks and COALECSE should be COALESCE. How do I do p.*, If I don't want to individually mention column names like p.Id and p.Price. SELECT p.*, COALESCE(pt.name, p.name) name, COALESCE(pt.description, p.description) description FROM Product p LEFT JOIN Product_T pt ON pt.PID = p.ID AND pt.LANG = 'ch' is giving name and description twice. – geekprogrammer Aug 28 '15 at 04:22
  • `Select *` is highly frowned upon. – Joel Coehoorn Aug 28 '15 at 04:23
  • Maybe, but in my case I require all the columns to be selected. In the example I just gave id and price for simplicity. My actual table has lot more columns. Anyway to do ? – geekprogrammer Aug 28 '15 at 04:25
  • You can still select `p.*`, and then just give these two columns different names, and only show those names.... but needing all the columns is irrelevant. If you need all of the columns, ask for them all by names. `select *` is bad juju. – Joel Coehoorn Aug 28 '15 at 04:26
0

In your case, you should use LEFT JOIN

SELECT p.ID, p.PRICE, ISNULL(pt.NAME,p.NAME) AS NAME, p.DESCRIPTION 
FROM Product AS p 
    LEFT JOIN Product_Translations AS pt ON p.ID = pt.PID
WHERE pt.LANG = @UserLang

ISNULL in hsqldb http://hsqldb.org/doc/guide/builtinfunctions-chapt.html#bfc_general_functions

Edward N
  • 997
  • 6
  • 11