SQL multiple joins to same table

random_seed

Hi,

I am trying to create an sql statement that draws from a database table with id, name and sub fields. If the sub field is = 0 it is a root category, but if the sub field is equal to one of the id fields, it is the sub of the row with that id... With this statement I want to be able to populate List/menu fields with an infinate amount of sub categories. Below is an example:

Database layout:

id name sub

1 food 0
2 fruit 1
3 apples 2

food is a root category with fruit being a sub category of food and apples being a sub category of fruit.

The code I have is below. It only gives me one level of sub categories which is very limiting.

SELECT c2.id, case when c1.name is null then c2.name else concat(c1.name, ' > ', c2.name) end as name
FROM mod_infobase_category c2 left join mod_infobase_category c1 on c2.sub=c1.id
ORDER BY name

Out puts:

fruit > apples

What I need is for it to output is:

food
food > fruit
food > fruit > apples

I think I need multiple joins in the SQL statement... anyone have any ideas?

Tim

nemonoman

You can join to the same table through aliasing. You'll see the using as, but you can also do it with a space. It's often done with a bill of materials or geneology type query

SELECT dad.name
FROM person child,
person dad
WHERE child.father_id=dad.id

Or for the purists:
SELECT dad.name
FROM person AS child,
person AS dad
WHERE child.father_id=dad.id

random_seed

Thanks. That makes sense. What I need to do is get the name field to output its name with its sub and its subs sub, so it will output "food > fruit > apples". Should I be using loops? Not very experienced with sql, so any help is appreciated...

I'll give this some more thought... Im way too tired right now to think straight anyway 😉

nemonoman

MySQL -- at least the version I'm using -- doesn't have the ability to do a drill down like you are describing. Oracle had a 'CONNECT BY' command with LEVELS, so you could specify the sort of query you're aiming at.

You'll have to build a loop in PHP where the target in the MySQL WHERE clause is updated by the result of the previous SELECT