Script question?

[Anon]

Hi all,
Let's say i have the following table structures:

table categories:

id int
parent int -- 0 for the main page
title text
descr text

table businesses:

id int
cat_id int
name text
descr text

And the data in the tables looks something like:

categories:
1, 0, 'Home'
2, 1, 'Shopping'
3, 1, 'Auto'
17, 3, 'Rentals'
18, 3, 'Repairs'

businesses:
351, 17, 'Cheap-a-rent'
345, 18, 'Ferrari Service Center'

What i want to know is how can i get the output to resemble this kind of layout. eg.

Auto
Rentals | Repairs ----> all are links.

Any ideas on the kind of script i will need?

Cheers,
wills

[Anon]

Looks like you need to learn about table joins. I just did, and it really opened up my querying.

[tomhath]

If I follow what you are trying to do, you want to connect three rows from the categories table (id=3, 17, and 18) to the two rows in businesses (id=351 and 345). So if you think about it you have a five way join (the 3 rows from categories, plus the rows for Rental and Services from businesses). I didn't test this but it should get you started:

select c1.name as BusinessType, -- 'Auto'
c2.name as VendorType, -- 'Rental'
c3.name as ServiceType, -- 'Repairs'
b1.name as VendorName, -- 'Cheap-a-rent'
b2.name as ServiceName -- 'Ferrari Service Center'
from categories c1, categories c2, categories c3,
businesses b1, business b2
where c1.name="Auto"
and c2.parent=c1.id and c2.name="Rentals"
and c3.parent=c1.id and c3.name="Repairs"
and b1.cat_id = c2.id
and b2.cat_id = c3.id