How to join multipl tables?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sweahe
Forum Commoner
Posts: 52
Joined: Sat May 04, 2002 4:07 am
Location: Växjö, Sweden

How to join multipl tables?

Post 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'
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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
sweahe
Forum Commoner
Posts: 52
Joined: Sat May 04, 2002 4:07 am
Location: Växjö, Sweden

Post 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
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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..
Post Reply