[RESOLVED] Join problem

[ChetUbetcha]

Hypothetically, I have two tables:

1. main_office, with field main_office (unique)

2. sub_office, with fields main_office (matches above main_office) & sub_office

If main_office table field main_office is populated with:
MAIN1
MAIN2
MAIN3

and sub_office table has fields main_office and sub_office populated with:
MAIN1, SUB1
MAIN1, SUB2
MAIN1, SUB3
MAIN2, SUB1
MAIN2, SUB2
MAIN3, SUB1

I want to find all sub_offices from the 2nd table (sub_office) that match to their corresponding main_office from the first table (main_office).

I'd like the display to be:
MAIN 1
...SUB1
...SUB2
...SUB3

MAIN 2
...SUB1
...SUB2

MAIN 3
...SUB1

How do I do this? JOIN? LEFT JOIN? INNER JOIN? And how do I get it to go through everything and have main_office listed once with all the associated sub_office fields listed below?

[troybtj]

I'm not sure about formatting the display, but the query would be along these lines:

SELECT main.office_name, sub.sub_office from main_offfice as main, sub_office as sub WHERE sub.main_office = main.office_name ORDER BY office_name, sub_office;

[ChetUbetcha]

Thanks for all the help!!! 😉 I was able to find the answer on another site: parent/child relationship.