Page 1 of 1

[SOLVED] Making this Access SQL query MySQL friendly

Posted: Fri Oct 14, 2005 3:41 am
by Skittlewidth
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)

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

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;
But that hasn't worked either (unsurprisingly). It's so long it's confusing me. Can someone help?

Posted: Fri Oct 14, 2005 5:38 am
by Weirdan
apparently mysql doesn't like parens in join clause, try this:

Code: Select all

FROM
   KF_status_codes
INNER JOIN
   KF_Members
    
ON
   KF_status_codes.Status_Code = KF_Members.Status_Code

INNER JOIN
         KF_Address
      ON
      KF_Members.Member_Number = KF_Address.Member_Number
INNER JOIN
   KF_Subscriptions
ON
   KF_Members.Member_Number = KF_Subscriptions.Member_Number

Posted: Fri Oct 14, 2005 5:54 am
by Skittlewidth
Excellent thanks!