Say I have one table (products) with these fields:
id (primary key)
product_name_id
product_descr_id
containing this:
1, 23, 300
2, 45, 324
...
and one table (lang) with these fields: (id and lang is primary key)
id
lang
lang_data
containing this:
23, 'sv', 'En bra product'
23, 'en', 'A good product'
300, 'sv', 'Jag tycker du ska köpa den här'
300, 'en', 'I think you should but this'
...
If I select product_id='1' I want a row where the
product_name_id (23) is swapped to the text in the lang table
and the product_descr_id (300) is swapped against the corresponding
text too for a choosen language.
How would I write that select statement?
SELECT * FROM products, lang WHERE products.id='1' AND lang='en' AND..??
Resulting in a row like:
1, 'A good product', 'I think you should buy this'
How to join multipl tables?
Moderator: General Moderators
You should split it up, create one column for product name and one for product description..
SELECT product_name,product_description FROM product
LEFT JOIN language_text ON language_text.product.id = product.product_id
WHERE language = 'EN'
product:
product_id, product_price, product_sku, product_stock, etc
language_text:
product_id, language, product_name, product_description
PRIMARY KEY is composed of product_id + language
SELECT product_name,product_description FROM product
LEFT JOIN language_text ON language_text.product.id = product.product_id
WHERE language = 'EN'
product:
product_id, product_price, product_sku, product_stock, etc
language_text:
product_id, language, product_name, product_description
PRIMARY KEY is composed of product_id + language
I don't want to do that, because I want the language table to be flexible, I want it to be a table for all tables I have in the database, holding the language texts for all kinds of fields in whatever table.
Isn't it possible to get all info in one select, having the numbers replaced with the text somehow?
/Andreas
Isn't it possible to get all info in one select, having the numbers replaced with the text somehow?
/Andreas
the only way to do that is retrieving multiple rows..
You will receive two rows, but you have to find an easy way to distinguis which is what.. no matter how you look at the two approaches, this is not much more dynamical as the query just moves the specifics to the where clause and more complexity in the presentation code..
Code: Select all
SELECT lang_text_id,language_text FROM language_table
WHERE
language='EN'
AND (
lang_text_id = product_name_id
OR
lang_text_id = product_descr_id
)