0 Replies Latest reply on Feb 5, 2008 7:22 AM by (Neil_Eisenberg)

    Complex query problem - cant access recordset

    Level 1
      I have a voting site and I am trying to get data from multiple tables - all linked with foreign keys to the vote table, so that I can calculate who wins each state/region/country etc.

      I finally had someone help me to write the query that gives me the results I want, and when I add it to create the recordset and use the "test" button it returns the data. I also tested the query in phpMyAdmin and it worked there too.

      The problem is that when I click on the created recordet to view the fields, or try to use a behavior like repeat region, conditional region etc.. it throws an error:

      [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS vdata ON vdata.Location = state.Continent' at line 2]

      this is the query. I have never used vdata in a query before, but it works andI couldnt figure out another way of doing it....

      Can anyone tell me what I am doing wrong? I have been working on this for three days.

      SELECT state.ID
      , state.ShortName
      , state.Name
      , state.state_URL
      , state.Continent
      , vdata.total_votes
      , vdata.PartyID_1_votes
      , vdata.PartyID_2_votes
      FROM state
      LEFT OUTER
      JOIN ( SELECT Location
      , COUNT(*) AS total_votes
      , SUM(CASE WHEN PartyID = 1
      THEN 1 ELSE 0 END)
      AS PartyID_1_votes
      , SUM(CASE WHEN PartyID = 2
      THEN 1 ELSE 0 END)
      AS PartyID_2_votes
      FROM votes
      GROUP BY Location
      ) AS vdata
      ON vdata.Location = state.Continent

      Neil