I have more tables, which I want to combine together for generate a CSV file what our backoffice system can read. I have 1 table with Product information and an other table with language information. The databases are build up as the following example:
Product database:
<table>
<tr>
<td>id</td><td>productnumber</td><td>price</td><td>name_id</td>
</tr>
<tr>
<td>1</td><td>ABC123</td><td>10.00</td><td>1</td>
</tr>
<tr>
<td>2</td><td>DEF456</td><td>15.00</td><td>2</td>
</tr>
</table>Name database:
<table>
<tr>
<td>id</td><td>name_id</td><td>name</td><td>language_id</td>
</tr>
<tr>
<td>1</td><td>1</td><td>Cheese</td><td>1</td>
</tr>
<tr>
<td>2</td><td>1</td><td>Fromagi</td><td>2</td>
</tr>
<tr>
<td>3</td><td>1</td><td>Kase</td><td>3</td>
</tr>
<tr>
<td>4</td><td>2</td><td>Water</td><td>1</td>
</tr>
<tr>
<td>5</td><td>2</td><td>Acqua</td><td>2</td>
</tr>
<tr>
<td>6</td><td>2</td><td>Wasser</td><td>3</td>
</tr>
</table>Language database:
<table>
<tr>
<td>id</td><td>Language</td>
</tr>
<tr>
<td>1</td><td>English</td>
</tr>
<tr>
<td>2</td><td>Italian</td>
</tr>
<tr>
<td>3</td><td>German</td>
</tr>
</table>What I want to have as output for the CSV file is like this:
Productnumber + Price + Name_EN + Name_IT + Name_DE
So:
ABC123 | 10.00 | Cheese | Fromagi | Kase
DEF456 | 15.00 | Water . | Acqua . . | Wasser
Is there a way how to achieve this?
If you need more information, just let me know! Thank you all in advance.
Best regards,
Koen de Haan
You can join the name table multiple times with the Product table.
select p.id, p.productnumber, p.price, en.name, it.name, de.name
from Product p
join Name en on en.name_id=p.name_id and en.language_id=1
join Name it on it.name_id=p.name_id and it.language_id=2
join Name de on de.name_id=p.name_id and de.language_id=3
For the future, it is better to show the actual table create script rather than some HTML code.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments