sql - inner join - PHPBuilder Forums

sql - inner join

Anon

Hi.....

I have a table as follows:

section_id
section_parent_id
section_name

And another one,

product_id
product_section_id
product_name
product_price
product_info

And I use the section table to store values for the product's sections, like

section | product

computers | disk drive
flowers | daisys

and so on.

The question is, sometimes I need a section to be within another section, like

computers -> hardware -> (products)

That's why I use the section_parent_id field, to store a section's parent section.

All fine until I need to select the results from the section table and in the ones that have the parent_id appear with that parent's name, like

id | parent | name
1 | 0 | some
2 | 0 | stupid
3 | 1 | stone
4 | 2 | thing

Would appear has

How does the SQL Query for something like this look ?

Thanks.

Anon

I managed to do it with a inner join to the same table AND another select to get the ones that have no section (top level sections).

Then I just merge the two arrays, and get a working solution.

BUT... I've been trying do an LEFT JOIN in the same query to get the ones with 0 parent_id

I'm with something like:

$sql = " select distinct cat1.categoria_id, cat1.categoria_titulo as titulo, cat2.categoria_id, cat2.categoria_titulo, cat2.categoria_parent_id, cat3.categoria_id, cat3.categoria_titulo from categoria cat1 inner join categoria cat2 on cat2.categoria_parent_id = cat1.categoria_id left join categoria cat3 on cat3.categoria_parent_id = 0 ";

wich is not producing the desired effect.

Thanks.

tomhath

Can you combine the two SELECT statements with a UNION ?