[SOLVED] Making this Access SQL query MySQL friendly

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

[SOLVED] Making this Access SQL query MySQL friendly

Post 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?
Last edited by Skittlewidth on Fri Oct 14, 2005 6:16 am, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post by Skittlewidth »

Excellent thanks!
Post Reply