Trouble Converting Sybase -> MySql

Wilhelm Murdoch · #1 (**permalink**) 07-27-07, 11:44

Hello all, this is my first post here, so I'm hoping I can get some help!

We're currently migrating our entire system over to a MySql database backend from Sybase. I'm familiar with Sybase at best, but I've run into a few problems concerning one of the more complicated queries.

The original SyBase version is right here:

Code:

SELECT
	a.quiz_id,
	a.quiz_name,
	b.intro,
	b.icon,
	(SELECT SUM(total)  FROM pin_dbo.fb_pq_results_totals c WHERE c.quiz_id = a.quiz_id) AS total_results,
	(SELECT d.votes_avg FROM pin_dbo.fb_pq_quiz_ratings   d WHERE d.quiz_id = a.quiz_id) AS votes_avg
FROM
	pin_dbo.pq_quiz a,
	pin_dbo.pq_quiz_options b,
	pin_dbo.fb_pq_quiz_ratings d
WHERE
	a.quiz_id NOT IN (5662,5250,3637,2977,2279,565,29,27,24,23,21) AND
	a.quiz_id      *= b.quiz_id AND
	a.quiz_id      *= d.quiz_id AND
	a.active        = 1 AND
	a.user_created  = 0
ORDER BY votes_avg DESC

I know that the '*=' signifies a LEFT JOIN, so I started the following MySql version:

Code:

SELECT
	a.quiz_id,
	a.quiz_name,
	b.intro,
	b.icon,
	(SELECT SUM(total)  FROM pin_dbo.fb_pq_results_totals c WHERE c.quiz_id = a.quiz_id) AS total_results,
	(SELECT d.votes_avg FROM pin_dbo.fb_pq_quiz_ratings   d WHERE d.quiz_id = a.quiz_id) AS votes_avg
FROM
	pin_dbo.pq_quiz_options b,
	pin_dbo.fb_pq_quiz_ratings d
LEFT JOIN pin_dbo.pq_quiz a ON a.quiz_id = d.quiz_id AND a.quiz_id = b.quiz_id
WHERE
	a.quiz_id NOT IN (5662,5250,3637,2977,2279,565,29,27,24,23,21) AND
	a.active       = 1 AND
	a.user_created = 0
ORDER BY votes_avg DESC

I know it isn't complete, I'm still working on it, but for some odd reason I get the following error from MySql when I attempt to test it:

Quote:

Unknown column 'b.quiz_id' in 'on clause'

I find this odd since the column 'b.quiz_id' does exist within the 'e_dbo.pq_quiz_options' table. You can also see that the table alias for that table, b, is also declared.

I'm kind of at a loss and this issue is sort of blocking the rest of my progress. Can anyone give me some pointers?

aschk · #2 (**permalink**) 07-27-07, 12:13

You're attempting to join
pin_dbo.pq_quiz a
ON
this clause .... a.quiz_id = d.quiz_id AND a.quiz_id = b.quiz_id
which won't work

A join clause can only have ON where's on columns that exist in the table you are joining it to.
thus you should have

FROM
pin_dbo.pq_quiz a
LEFT JOIN pin_dbo.fb_pq_quiz_ratings d ON a.quiz_id = d.quiz_id
LEFT JOIN pin_dbo.pq_quiz_options b ON a.quiz_id = b.quiz_id

or something close to the above.
I have not studied your example well enough to say for certain this query will give you the expected result.

Wilhelm Murdoch · #3 (**permalink**) 07-27-07, 12:16

Well that worked perfectly! Not sure why I thought you could use an 'AND' within the JOIN clause, but thanks!

Thanks, thanks, thanks!!!

aschk · #4 (**permalink**) 07-27-07, 12:28

You CAN use AND with the JOIN, but they have to match ONLY the columns you are utilising in the adjoining table. i.e. FROM table1 a JOIN table2 b ON a.id=b.id AND a.name=b.name

Wilhelm Murdoch · #5 (**permalink**) 07-27-07, 16:17

Gotcha!

Thanks again!

Internet Services

Web Development

Digital Marketing

Consumer Tech