From: Peter Brawley
Date: May 2 2005 2:44pm
Subject: Re: joining six tables by mutual column
List-Archive: http://lists.mysql.com/mysql/183496
Message-Id: <42763CB6.6050804@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Schalk,

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

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

PB

-----

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
>
> 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