List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:May 2 2005 2:51pm
Subject:Re: joining six tables by mutual column
View as plain text  
Schalk Neethling wrote:

> Mathias/Everyone on the list
> 
> I am running the following query against the database:
> 
> SELECT demographic.demographic_no, demographic.first_name, 
> demographic.last_name, demographic.chart_no, demographic.sex, 
> demographic.year_of_birth, demographic.month_of_birth, 
> demographic.date_of_birth, demographic.family_doctor, 
> demographic.roster_status, demographic.patient_status, demographic.phone 
> FROM allergies, demographic, drugs, echart, dxresearch, ichppccode WHERE 
> demographic.demographic_no = '1' AND demographic.last_name LIKE 'TES' 
> AND allergies.demographic_no = demographic.demographic_no AND 
> demographic.demographic_no = drugs.demographic_no AND 
> drugs.demographic_no = dxresearch.demographic_no AND 
> dxresearch.demographic_no = echart.demographicNo AND 
> allergies.demographic_no = echart.demographicNo AND 
> dxresearch.dxresearch_code = ichppccode.ichppccode

First, some general advice.  As presented above, your query is very 
difficult to read.  People who could help you may choose to ignore you 
if they have to work too hard to understand your question.  You should 
take a few minutes to rewrite your query in an easily readable format, 
so those who would help you don't have to.  As an example, here's your 
query reformatted:

   SELECT demographic.demographic_no,
          demographic.first_name,
          demographic.last_name,
          demographic.chart_no,
          demographic.sex,
          demographic.year_of_birth,
          demographic.month_of_birth,
          demographic.date_of_birth,
          demographic.family_doctor,
          demographic.roster_status,
          demographic.patient_status,
          demographic.phone
   FROM allergies, demographic, drugs, echart, dxresearch, ichppccode
   WHERE demographic.demographic_no = '1'
     AND demographic.last_name LIKE 'TES'
     AND allergies.demographic_no = demographic.demographic_no
     AND demographic.demographic_no = drugs.demographic_no
     AND drugs.demographic_no = dxresearch.demographic_no
     AND dxresearch.demographic_no = echart.demographicNo
     AND allergies.demographic_no = echart.demographicNo
     AND dxresearch.dxresearch_code = ichppccode.ichppccode;

Longer, yes, but much easier to read.

Now that I can read it, two things jump out.  Assuming demographic_no is 
a number, you would save a string to number conversion if you would 
leave out the quotes around the desired number, 1.  Secondly, equality 
comparisons seem to be faster than LIKE comparisons.  If there is no 
wildcard in the string to match, use = instead of LIKE.

> Is the string at the end from, 'AND allergies.demographic_no = 
> demographic.demographic_no...', the correct way to create the JOIN I 
> require by demographic_no on five of the tables and then a JOIN of the 
> dxresearch and ichppccode tables by dxresearch_code and ichppccode 
> respectively? Any help or pointers would be much appreciated. Thank you!

Yes, this should work, but you would be better served if you took Shawn 
Green's standard advice: Use explicit JOIN conditions.  That is, move 
the JOIN criteria from the WHERE clause to the ON part of the JOIN 
clause.  If you rewrite the above that way, you will notice that you 
have redundant conditions:

   dxresearch.demographic_no = echart.demographicNo

and

   allergies.demographic_no = echart.demographicNo

Either one is sufficient to identify the correct row in echart.

Putting all this together, I'd suggest:

   SELECT demographic.demographic_no,
          demographic.first_name,
          demographic.last_name,
          demographic.chart_no,
          demographic.sex,
          demographic.year_of_birth,
          demographic.month_of_birth,
          demographic.date_of_birth,
          demographic.family_doctor,
          demographic.roster_status,
          demographic.patient_status,
          demographic.phone
   FROM allergies
   JOIN demographic
        ON allergies.demographic_no = demographic.demographic_no
   JOIN drugs ON demographic.demographic_no = drugs.demographic_no
   JOIN dxresearch ON drugs.demographic_no = dxresearch.demographic_no
   JOIN echart ON dxresearch.demographic_no = echart.demographicNo
   JOIN ichppccode ON dxresearch.dxresearch_code = ichppccode.ichppccode
   WHERE demographic.demographic_no = 1
     AND demographic.last_name = 'TES';

(I take it demographic.demographic_no is not the PRIMARY KEY in table 
demographic?)

Michael
Thread
joining six tables by mutual columnSchalk Neethling25 Apr
  • Re: joining six tables by mutual columnRichard Lynch25 Apr
  • RE: joining six tables by mutual columnmathias fatene25 Apr
    • Re: joining six tables by mutual columnSchalk Neethling2 May
      • Re: joining six tables by mutual columnPeter Brawley2 May
      • Re: joining six tables by mutual columnMichael Stassen2 May
  • Re: joining six tables by mutual columnNick Pasich25 Apr