CODEWITHSUNDEEP
sqlsql-serverjoin
BBaxter
BBaxter

Reputation: 145

Joining multiple fields between the same tables

I have a table called 'Resources' that looks like this:

   Country        City       Street      Headcount
      UK         Halifax   High Street      20
United Kingdom   Oxford    High Street      30
    Canada       Halifax    North St        40

Because of the nature of the location fields, I need to map them to a single 'Address' field, and so I also have the following table called 'Addresses':

   Country        City       Street                 Address
      UK         Halifax   High Street     High Street, Halifax, UK
    Canada       Halifax    North St     North Street, Halifax, Canada
United Kingdom   Oxford    High Street      High Street, Oxford, UK

(In reality the Address field does add information rather than just combining what is already there.)

I am currently using the following SQL to produce the query:

SELECT Resources.Country, Resources.City, Resources.Street, Addresses.Address,
       Resources.Headcount
FROM   Resources
INNER JOIN Addresses ON Resources.Country = Addresses.Country
       AND Resources.City = Addresses.City
       AND Resources.Street = Addresses.Street

This works for me, but I am worried that I have not seen people use this many ANDs in a single join elsewhere, so don't know if it is a bad idea. (This is simplified version - I may need up to 8 ANDs in a single join in another case) Is this the best way to approach the problem, or is there a better solution?

Thanks

Upvotes: 1

Views: 83

Answers (1)

granadaCoder
granadaCoder

Reputation: 27852

Joining on multiple columns is fine. You don't have to "fear" this.

As far as "a better way". I would suggest creating some variable tables, putting some data in them, and posting that TSQL (DDL and DML) here. Then you can get some possible alternatives. Your question is vague at the present (in regards to the "is there a better way" portion of your question)

Upvotes: 1

Related Questions