CODEWITHSUNDEEP
mysqljoin
user648198
user648198

Reputation: 2020

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

Upvotes: 0

Views: 52

Answers (2)

Pentium10
Pentium10

Reputation: 207893

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.

Upvotes: 1

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

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

Upvotes: 1

Related Questions