I need help with Joining tables in MYSQL

Currently I'm developing a website with MYSQL INNODB and I need someone to help me out how to join tables so I can match the providers with the quote

Here is my tables structure

quote    - quoteId,postcodeId
provider - providerId
areaProviderPreference - postcodeId,providerId
postcode - postcodeId

Basically I need to write a MYSQL query which return a list of all the providers which cover with the quote postcodeId. Can someone help me out please :) I'm totally struggling with it!开发者_运维问答

Thanks so much in advance.

Cheers

select p.* from provider p
inner join areaProviderPreference ap on p.providerID = ap.providerID
inner join quote q on ap.postcodeId = q.postcodeID

In addition if you need postCode details also, then you need an additional join as

inner join PostCode pc on pc.postCodeId = ap.postcodeId

SELECT p.providerid 
FROM   provider p 
       JOIN areaproviderpreference app 
         ON app.providerid = p.providerid 
       JOIN quote q 
         ON q.quoteid = app.postcodeid 
GROUP  BY p.providerid 

you need indexes on

• (areaproviderpreference.providerid)
• (quote.quoteid)
• (provider.providerid)

Actually you don't event need JOIN quote, but you may want to use a WHERE clause there.