0

I have two tables, ingredient and ingredient_language.

I need to display (from SQL) all translations of each ingredient, and in case that I did not have any translations for some language, in the rows have to appear like this, for example: 

Language | Ingredient
ENG        Salt
GER        NULL

How can I display it?

The structure of the tables are

Ingredient{id_ingredient}
Ingredient_Language{id_ingredient, id_language, traduction}

Thanks.

EDIT:

SELECT i.id_ingredient, l.id_language, l.trad
FROM Ingredient i
  LEFT OUTER JOIN Ingredient_language l ON l.id_ingredient = i.id_ingredient

Example

Okey, the structure are:

·Table 1 (Ingredient) > Columns: Id_ingredient (PK), standard_name
·Table 2 (Language) > Columns: ID_Language (PK), name_language 
·Table 3 (Ingredient_Language) > Columns: ID_Ingredient_Language (PK),ID_ingredient (FK), ID_LANGUAGE(FK), description

Example of data (Can not do any screen at this moment):

 ·Table 1: 01, Orange
 ·Table 2: SP, SPANISH
           ENG, ENGLISH
 ·Table 3: 01, 01, SP, Naranja

What I need to get? The following example:

Header: id_language, id_ingredient, description
Row1:      SP            01            NARANJA
Row2:      ENG           01              NULL

Thanks for the help.

Nega developer
  • 259
  • 3
  • 8
  • 19
  • You are looking for an outer join –  Feb 04 '16 at 10:59
  • do you have a language table? – A ツ Feb 04 '16 at 11:00
  • "*did not work*" is not a valid error message (and not an acceptable problem description). Also: do **not** post code in comments. [Edit](http://stackoverflow.com/posts/35199228/edit) your question –  Feb 04 '16 at 11:10
  • @a_horse_with_no_name I will try to do best: My problem persists because in that case when I try to do with an outer join, my query only displays the ingredients (with traduction) that i have in ingredient_language, not in both. – Nega developer Feb 04 '16 at 11:18
  • Again: **edit** your question and add the query you are using. –  Feb 04 '16 at 11:18
  • i repeat my question: do you have a language table? – A ツ Feb 04 '16 at 11:24
  • You have some basic problem - do you want the language or the ingredient as the basic data? If it is the language than start your join from the language and not from the ingridient. – Tarsis Feb 04 '16 at 11:27
  • Just a tip, name the table `ingredients` (plural, for several rows with ingredients), and the column `ingredient` (singular, for one ingredient). – jarlh Feb 04 '16 at 11:30
  • Singular table names are common practice, a table nearly allways contains multiple rows so there is no sense in plural naming. – Tarsis Feb 04 '16 at 11:33
  • Please tag your question with the database you are using. – Gordon Linoff Feb 04 '16 at 11:49
  • @Tarsis I Tryed with your last edit, but having the same problems. I am using Interbase – Nega developer Feb 04 '16 at 14:03
  • Sorry i am not going to do any more guessing, if you need help give proper information: post the structure of all 3 tables including at least some rows of data so we can understand what a join will actually do. – Tarsis Feb 05 '16 at 08:50

2 Answers2

1
SELECT l.traduction,l.id_language,i.id_ingredient 
FROM Ingredient_Language AS l 
  LEFT OUTER JOIN Ingredient AS i ON l.id_ingredient = i.id_ingredient

Just change the SELECT part to your needs.

Just a guess, you might actually wanna begin the join from your language table like

SELECT l.id_language,i.id_ingredient 
FROM Language l
  LEFT OUTER JOIN Ingredient_Language il ON l.id_language = il.id_language 
  LEFT OUTER JOIN Ingredient i ON li.id_ingredient = i.id_ingredient
Tarsis
  • 712
  • 6
  • 14
0

You want to start with a cross join, which combines every ingredient with every language:

SELECT i.id_ingredient, l.id_language, il.trad
FROM Ingredient i
CROSS JOIN Language l

and add all existing translations to that with

LEFT JOIN Ingredient_language il 
   ON  il.id_ingredient = i.id_ingredient 
   AND il.id_language = l.id_language

if you have a database which does not support CROSS JOIN you can try 

SELECT i.id_ingredient, l.id_language, il.trad
FROM Ingredient i
JOIN Language l ON 1=1
LEFT JOIN Ingredient_language il 
   ON  il.id_ingredient = i.id_ingredient 
   AND il.id_language = l.id_language
A ツ
  • 1,267
  • 2
  • 9
  • 14