pulling data from multiple tables

[.josh]

okay, so i have a list of providers and a list of services. since different providers can offer more than one service, i decided to setup my database as such:
[!--coloro:blue--][span style=\"color:blue\"][!--/coloro--]
[!--coloro:red--][span style=\"color:red\"][!--/coloro--]<table>[!--colorc--][/span][!--/colorc--] [b]providers[/b]
[!--coloro:red--][span style=\"color:red\"][!--/coloro--]<column>[!--colorc--][/span][!--/colorc--]provider_id [!--coloro:green--][span style=\"color:green\"][!--/coloro--](unique, primary key, auto inc.) [!--colorc--][/span][!--/colorc--]
[!--coloro:red--][span style=\"color:red\"][!--/coloro--]<column>[!--colorc--][/span][!--/colorc--] provider_name [!--coloro:green--][span style=\"color:green\"][!--/coloro--](php script makes sure it is unique before new insertion)[!--colorc--][/span][!--/colorc--]

[!--coloro:red--][span style=\"color:red\"][!--/coloro--]<table>[!--colorc--][/span][!--/colorc--] [b]services[/b]
[!--coloro:red--][span style=\"color:red\"][!--/coloro--]<column>[!--colorc--][/span][!--/colorc--]service_id [!--coloro:green--][span style=\"color:green\"][!--/coloro--](unique, primary key, auto inc.)[!--colorc--][/span][!--/colorc--]
[!--coloro:red--][span style=\"color:red\"][!--/coloro--]<column>[!--colorc--][/span][!--/colorc--] service_name [!--coloro:green--][span style=\"color:green\"][!--/coloro--](php script makes sure it is unique before new insertion)[!--colorc--][/span][!--/colorc--]
[!--colorc--][/span][!--/colorc--]
and i thought I could somehow linkie the two with this 3rd table:
[!--coloro:blue--][span style=\"color:blue\"][!--/coloro--]
[!--coloro:red--][span style=\"color:red\"][!--/coloro--]<table>[!--colorc--][/span][!--/colorc--][b]maintable[/b]
[!--coloro:red--][span style=\"color:red\"][!--/coloro--]<column>[!--colorc--][/span][!--/colorc--]provider_id
[!--coloro:red--][span style=\"color:red\"][!--/coloro--]<column>[!--colorc--][/span][!--/colorc--]service_id
[!--colorc--][/span][!--/colorc--]

okay so i've been able to successfully insert everything properly into these tables, with php. on my webpage I have a textfield where you can input a new service name, and after checking to make sure it doesn't exist in table services, it inserts a new row into it.

in addition, i have a form where you can enter a new provider. you enter the new provider name into the textfield and the form shows the current list of services available, along with checkboxes for each one. the user can check several of them or just one. when they submit the form, it checks to make sure the provider name doesn't already exist, and also makes sure at least one service was selected for this new provider.

then i insert a new row into table providers, and then get the auto generated provider_id number (from when the row is created) and then for each service selected, I insert a new row into table maintable with the service_id number and provider_id number.

this all works just fine.

what i want to be able to do, on a different webpage, is list all providers and current services that they offer.

so the question is this: did I set up this database correctly, for doing that? and if so, how would I go about querying the database to do that?

i thought the query string should look something like this:

[i][!--coloro:blue--][span style=\"color:blue\"][!--/coloro--]SELECT services.service_name, providers.provider_name FROM services, providers WHERE maintable.service_id = services.service_id AND maintable.provider_id = providers.provider_id[!--colorc--][/span][!--/colorc--][/i]

but it returns this error: #1109 - Unknown table 'maintable' in where clause

so i tried the same thing with quotes:

[i][!--coloro:blue--][span style=\"color:blue\"][!--/coloro--]SELECT services.service_name, providers.provider_name FROM services, providers WHERE 'maintable.service_id' = 'services.service_id' AND 'maintable.provider_id' = 'providers.provider_id'[!--colorc--][/span][!--/colorc--][/i]

and the query returns no errors but it says that zero results were found. now, i have looked in the database and the data does exist. because:

provider1 offers services 1 and 2
provider2 offers services 3 and 2
provider3 offers services 1,2 and 3

this is what I have in my database right now:

table providers
provider_id: 1 | provider_name: provider1
provider_id: 2 | provider_name: provider2
provider_id: 3 | provider_name: provider3

table services
service_id: 1 | service_name: service1
service_id: 2 | service_name: service2
service_id: 3 | service_name: service3

table maintable
provider_id: 1 | service_id: 1
provider_id: 1 | service_id: 2
provider_id: 2 | service_id: 3
provider_id: 2 | service_id: 2
provider_id: 3 | service_id: 1
provider_id: 3 | service_id: 2
provider_id: 3 | service_id: 3

shouldn't the query be returning this? :

[b]providers | services[/b]
provider1 | service1
provider1 | service2
provider2 | service3
provider2 | service2
provider3 | service1
provider3 | service2
provider3 | service3

in any case, that's what i want it to return, so how do i go about doing that? (sorry for the lengthy post)

[.josh]

I just wanted to post that I figured out how to do it. For those who are interested, the correct query is as follows:

[!--coloro:blue--][span style=\"color:blue\"][!--/coloro--][i]
select providers.provider_name
, services.service_name
from providers
inner
join maintable
on maintable.provider_id = providers.provider_id
inner
join services
on services.service_id = maintable.service_id
order
by providers.provider_name
, services.service_name
[/i][!--colorc--][/span][!--/colorc--]

hmmm i thought there was a mark as solved button around here somewheres....

[fenway]

Sounds about right -- obviously, you needed to actually include the maintable in your query. BTW, be aware that using that ORDER BY clause will trigger a temp table/filesort.