List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 2 2005 2:44pm
Subject:Re: joining six tables by mutual column
View as plain text  

Your query looks ok except for an apparently missing '%' in LIKE 'TES', 
but all the WHEREs are hard to read, aren't they? I find queries easier 
to write, read & maintain with joins in the JOIN clause and specific 
row-selection criteria in the WHERE clause, eg

  demographic.demographic_no, ... etc ...
FROM demographic
  INNER JOIN allergies ON demographic.demographic_no = 
  INNER JOIN drugs ON demographic.demographic_no - drugs.demographic_no
  INNER JOIN research ON demographic.demographic_no = 
  INNER JOIN echart ON demographic.demographic_no = echart.demographic_no
  INNER JOIN dxresearch ON demographic.demographic_no = 
  INNER JOIN ichppccode ON dxresearch.dxresearch_code = 
WHERE demographic.demographic_no = '1'
  AND demographic.last_name LIKE 'TES%'



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.year_of_birth, demographic.month_of_birth, 
> demographic.date_of_birth, demographic.family_doctor, 
> demographic.roster_status, demographic.patient_status, 
> 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
> 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!
> mathias fatene wrote:
>> Hi,
>> Select * from table1 T1, table2 T2, table3 T3, table4 T4, table5 T5,
>> table6 T6 Where T1.col=T2.col
>> and T2.col=T3.col
>> and T3.col=T4.col
>> and T4.col=T5.col
>> and T5.col=T6.col
>> and T1.col=T6.col
>> [and col='val']
>> Doesn't this work ? Have you an example ?
>> Best Regards
>> --------------------
>> Mathias FATENE
>> Hope that helps
>> *This not an official mysql support answer
>> -----Original Message-----
>> From: Schalk Neethling [mailto:schalk@stripped] Sent: lundi 25 
>> avril 2005 00:52
>> To: mysql@stripped
>> Subject: joining six tables by mutual column
>> Greetings everyone.
>> Hope someone can give me some pointers here. I have six tables in the 
>> database and I need to JOIN them on a row that appears in all of the 
>> tables. How do I do this? I have so far done the normal 'cross-join' 
>> saying SELECT * FROM table1, table2, table3, table4, table5, table6 
>> WHERE something = something;
>> I have also added STRAIGHT_JOIN to force the order but, how do I JOIN 
>> six tables to/by one column? I have done some google searches as well as
>> looked at MySQL 2nd edition by Paul DuBois, sorry if I missed 
>> something here Paul, and so far I have not found an answer. Any help 
>> or pointers will be appreciated. Thank you.

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.1 - Release Date: 5/2/2005

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