sql query

Hello to all of you! I would appreciate if someone could help me out. There
are three tables in my database:

--------------------------------
| table: sections |
--------------------------------
| section_id | parent_id |
--------------------------------
1 0
2 1

-----------------------------------------------------
| table: section_descriptions |
-----------------------------------------------------
| section_id | language_id | section_name |
-----------------------------------------------------
1 1 Home
1 2 Caza
2 1 News
2 2 Notizie

------------------------------------------------------------
| table: languages
|
------------------------------------------------------------
| language_id | language_name | language_code |
------------------------------------------------------------
1 English en
2 Italino it

and I need a valid MySQL qury for output as shown below:

--------------------------------------------------------
| section_id | section_name | section_parent |
--------------------------------------------------------
2 News Home

or

--------------------------------------------------------
| section_id | section_name | section_parent |
--------------------------------------------------------
2 Notizie Caza


Thanks in advance!
---
iMi

Re: sql query

iMi wrote:
> Hello to all of you! I would appreciate if someone could help me out. There
> are three tables in my database:
>
> --------------------------------
> | table: sections |
> --------------------------------
> | section_id | parent_id |
> --------------------------------
> 1 0
> 2 1
>
> -----------------------------------------------------
> | table: section_descriptions |
> -----------------------------------------------------
> | section_id | language_id | section_name |
> -----------------------------------------------------
> 1 1 Home
> 1 2 Caza
> 2 1 News
> 2 2 Notizie
>
> ------------------------------------------------------------
> | table: languages
> |
> ------------------------------------------------------------
> | language_id | language_name | language_code |
> ------------------------------------------------------------
> 1 English en
> 2 Italino it
>
> and I need a valid MySQL qury for output as shown below:
>
> --------------------------------------------------------
> | section_id | section_name | section_parent |
> --------------------------------------------------------
> 2 News Home
>
> or
>
> --------------------------------------------------------
> | section_id | section_name | section_parent |
> --------------------------------------------------------
> 2 Notizie Caza
>
>

What did you try so far?

Would this work for you, just a simple join of all the tables?

SELECT a.section_id, b.section_name, d.section_name as section_parent FROM
sections a JOIN
sections c ON (a.parent_id=c.section_id) JOIN
section_descriptions b ON (b.section_id=a.section_id) JOIN
section_descriptions d ON (d.section_id=c.section_id) JOIN
languages l ON (l.language_id=d.language_id AND b.language_id=l.language_id)
WHERE l.language_code='en'

Regards
Stefan

> Thanks in advance!
> ---
> iMi
>
>

Re: sql query

> Hello to all of you! I would appreciate if someone could help me out. There
> are three tables in my database:
>
> --------------------------------
> | table: sections |
> --------------------------------
> | section_id | parent_id |
> --------------------------------
> 1 0
> 2 1
>
> -----------------------------------------------------
> | table: section_descriptions |
> -----------------------------------------------------
> | section_id | language_id | section_name |
> -----------------------------------------------------
> 1 1 Home
> 1 2 Caza
> 2 1 News
> 2 2 Notizie
>
> ------------------------------------------------------------
> | table: languages
> |
> ------------------------------------------------------------
> | language_id | language_name | language_code |
> ------------------------------------------------------------
> 1 English en
> 2 Italino it
>
> and I need a valid MySQL qury for output as shown below:
>
> --------------------------------------------------------
> | section_id | section_name | section_parent |
> --------------------------------------------------------
> 2 News Home
>
> or
>
> --------------------------------------------------------
> | section_id | section_name | section_parent |
> --------------------------------------------------------
> 2 Notizie Caza



SELECT
s1.section_id,
s1.section_name,
s2.section_name AS section_parent
FROM
sections AS sc INNER JOIN
section_descriptions AS s1 ON sec.section_id = s1.section_id LEFT OUTER JOIN
section_descriptions AS s2 ON sec.parent_id = s2.section_id AND s1.language_id = s2.language_id
WHERE
sc.section_id = 2 AND
s1.language_id = 1

or

SELECT
s1.section_id,
s1.section_name,
s2.section_name AS section_parent
FROM
sections AS sc INNER JOIN
section_descriptions AS s1 ON sec.section_id = s1.section_id LEFT OUTER JOIN
section_descriptions AS s2 ON sec.parent_id = s2.section_id AND s1.language_id = s2.language_id
WHERE
sc.section_id = 2 AND
s1.language_id = 2


Hilarion

PS.: If you use ASCII-art tables, then make sure you have selected some fixed-width
font in your newsreader application (this will not make everyone see your
ASCII-art OK, but at least it'll be OK for those, who use fixed-width
fonts when reading news).

Re: sql query

Thank a lot!

---
iMi

Re: sql query

Just perfect Stefan! This is exactly what I've been looking for, even more,
this query retrieves has only the 'sections' which have a parent. Thanks a
lot !

---
iMi

"Stefan Rybacki" wrote in message
news:3te12oFs5b71U1@individual.net...
> iMi wrote:
>> Hello to all of you! I would appreciate if someone could help me out.
>> There are three tables in my database:
>>
>> --------------------------------
>> | table: sections |
>> --------------------------------
>> | section_id | parent_id |
>> --------------------------------
>> 1 0
>> 2 1
>>
>> -----------------------------------------------------
>> | table: section_descriptions |
>> -----------------------------------------------------
>> | section_id | language_id | section_name |
>> -----------------------------------------------------
>> 1 1 Home
>> 1 2 Caza
>> 2 1 News
>> 2 2 Notizie
>>
>> ------------------------------------------------------------
>> | table: languages |
>> ------------------------------------------------------------
>> | language_id | language_name | language_code |
>> ------------------------------------------------------------
>> 1 English en
>> 2 Italino it
>>
>> and I need a valid MySQL qury for output as shown below:
>>
>> --------------------------------------------------------
>> | section_id | section_name | section_parent |
>> --------------------------------------------------------
>> 2 News Home
>>
>> or
>>
>> --------------------------------------------------------
>> | section_id | section_name | section_parent |
>> --------------------------------------------------------
>> 2 Notizie Caza
>>
>>
>
> What did you try so far?
>
> Would this work for you, just a simple join of all the tables?
>
> SELECT a.section_id, b.section_name, d.section_name as section_parent FROM
> sections a JOIN
> sections c ON (a.parent_id=c.section_id) JOIN
> section_descriptions b ON (b.section_id=a.section_id) JOIN
> section_descriptions d ON (d.section_id=c.section_id) JOIN
> languages l ON (l.language_id=d.language_id AND
> b.language_id=l.language_id)
> WHERE l.language_code='en'
>
> Regards
> Stefan
>
>> Thanks in advance!
>> ---
>> iMi