|
|
Posted by Hilarion on 10/13/23 11:31
> 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).
[Back to original message]
|