[SOLVED] Making this Access SQL query MySQL friendly
Posted: Fri Oct 14, 2005 3:41 am
I've been given the task of converting a 4 year old Access database over to MySQL, reports and all.
There's a wonderfully long query which used to generate a single table out of 4 of the 12 tables which was then exported to MySQL to power a major part of the client's website.
Now that I have imported all the tables in to MySQL I no longer need to have this extra table, but I do need to use the query that generated it so that I can recreate the dataset on the fly.
Here's where I'm running into difficulties. The query as it came out of Access goes as follows (I've removed the "[ ]"s and made sure all the table names are correct for the MySQL version)
Trouble is that doesn't seem to work in MySQL. It doesn't like the syntax at "inner join".
I've tried changing that to:
But that hasn't worked either (unsurprisingly). It's so long it's confusing me. Can someone help?
There's a wonderfully long query which used to generate a single table out of 4 of the 12 tables which was then exported to MySQL to power a major part of the client's website.
Now that I have imported all the tables in to MySQL I no longer need to have this extra table, but I do need to use the query that generated it so that I can recreate the dataset on the fly.
Here's where I'm running into difficulties. The query as it came out of Access goes as follows (I've removed the "[ ]"s and made sure all the table names are correct for the MySQL version)
Code: Select all
SELECT
DISTINCT
KF_Members.Member_Number,
KF_Members.First_Name,
KF_Members.Surname,
KF_Members.Status_Code,
KF_status_codes.Status_Description,
KF_Address.Clinic_Name,
KF_Address.Address_Line_1,
KF_Address.Address_Line_2,
KF_Address.Address_Line_3,
KF_Address.Address_Line_4,
KF_Address.Postcode,
KF_Address.County,
KF_Address.Country,
KF_Address.Contact_Phone_Number_1,
KF_Address.Contact_Phone_Number_2,
KF_Address.Email_Address,
KF_Address.Web_Site,
KF_Members.Specialities
FROM
(KF_status_codes
INNER JOIN
(KF_Members
INNER JOIN
KF_Address
ON
KF_Members.Member_Number = KF_Address.Member_Number)
ON
KF_status_codes.Status_Code = KF_Members.Status_Code)
INNER JOIN
KF_Subscriptions
ON
KF_Members.Member_Number = KF_Subscriptions.Member_Number
WHERE
(((KF_Members.Status_Code)
In
(1,2,4,9))
AND
((KF_Subscriptions.Sub_Year)>='2005')
AND
((KF_Address.Public_Details)=True))
ORDER BY
KF_Members.Member_Number;I've tried changing that to:
Code: Select all
SELECT
DISTINCT
KF_Members.Member_Number,
KF_Members.First_Name,
KF_Members.Surname,
KF_Members.Status_Code,
KF_status_codes.Status_Description,
KF_Address.Clinic_Name,
KF_Address.Address_Line_1,
KF_Address.Address_Line_2,
KF_Address.Address_Line_3,
KF_Address.Address_Line_4,
KF_Address.Postcode,
KF_Address.County,
KF_Address.Country,
KF_Address.Contact_Phone_Number_1,
KF_Address.Contact_Phone_Number_2,
KF_Address.Email_Address,
KF_Address.Web_Site,
KF_Members.Specialities
FROM
(KF_status_codes,
(KF_Members,
KF_Address
WHERE
KF_Members.Member_Number = KF_Address.Member_Number)
WHERE
KF_status_codes.Status_Code = KF_Members.Status_Code),
KF_Subscriptions
WHERE
KF_Members.Member_Number = KF_Subscriptions.Member_Number
WHERE
(((KF_Members.Status_Code)
In
(1,2,4,9))
AND
((KF_Subscriptions.Sub_Year)>='2005)
AND
((KF_Address.Public_Details)=True))
ORDER BY
KF_Members.Member_Number;