0

I have two tables in a database. One is the name of a provider (name, general info) the other is the type of provider. The provider can be more than one type. So in my provider database you can choose three different types they would be.

I am having trouble figuring out how to access the provider_type name. I assume a join but can't seem to get past that.

This is what I have now: 

SELECT p.name, p.phone, p.provider_type1, p.provider_type2 
FROM providers AS p 
INNER JOIN provider_type ON p.provider_type1 = provider_type.id
WHERE installation_id = $installation_id

The problem I keep running across is a provider that has more than one type they can be comes up in the query twice, rather I just want to list that provider then underneath list the type of services they can provide.

The join doesn't seem right. All I need to do is grab the provider_type name with the same id as providers.provider_type1 etc..

What should I be doing instead. Thanks.

davidkonrad
  • 83,997
  • 17
  • 205
  • 265
looloobs
  • 761
  • 2
  • 11
  • 24
  • 1
    You shouldn't have multiple `provider_type` columns. Normalize your data by creating a table with a separate row for each provider/type combination. – Barmar Feb 11 '15 at 18:01
  • @Barmar thanks, can you give me a more specific example of what you mean? Would it be as simple as adding a table with provider_id and provider_type_id? Thanks. – looloobs Feb 11 '15 at 19:19
  • Yes, that's basically it. – Barmar Feb 11 '15 at 19:26
  • Yeah -- a *tertiary* table. It allows you to build many-to-many relationships between your data. – haliphax Feb 11 '15 at 22:42

1 Answers1

0

Join with provider twice, with two aliases, one for each provider_type.

See this.

(But better to have a table for rows "provider [provider_id] has type (name) [provider_type]" instead of the provider_typeX columns. Although that's not normalization, just good design.)

philipxy
  • 14,867
  • 6
  • 39
  • 83