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
