Page 1 of 1

How to join multipl tables?

Posted: Sat Feb 08, 2003 8:08 pm
by sweahe
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'

Posted: Sun Feb 09, 2003 4:10 am
by Stoker
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

Posted: Sun Feb 09, 2003 4:27 am
by sweahe
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

Posted: Sun Feb 09, 2003 12:11 pm
by Stoker
the only way to do that is retrieving multiple rows..

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