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;