Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Exclamation Unanswered: Trouble Converting Sybase -> MySql

    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:
    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?
    Last edited by Wilhelm Murdoch; 07-27-07 at 12:52.

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  3. #3
    Join Date
    Jul 2007
    Posts
    3
    Well that worked perfectly! Not sure why I thought you could use an 'AND' within the JOIN clause, but thanks!


    Thanks, thanks, thanks!!!

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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

  5. #5
    Join Date
    Jul 2007
    Posts
    3
    Gotcha!

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •