MySQL subquery help

[Lance Gurd]

I have the following query (with subqueries)

SELECT all_jobs.REF_NO,
all_jobs.JOB,
Areas.ja_name AS Name,
fixers.name,
fixers.type AS Occupation
FROM ((SELECT job_areas.ja_no, job_areas.ja_name, job_areas.ref_no
FROM jobs.job_areas job_areas) Areas
INNER JOIN jobs.all_jobs all_jobs ON (Areas.ref_no = all_jobs.REF_NO))
INNER JOIN
(SELECT job_operatives.ref_no,
job_operatives.type,
labour.LONG_NAME AS name
FROM jobs.labour labour
INNER JOIN jobs.job_operatives job_operatives
ON (labour.FIXER_NO = job_operatives.op_name)) fixers
ON (fixers.ref_no = all_jobs.REF_NO)  

This works in TOAD and MySQL Workbench, in the SQL query builder it gives the correct resultset, but gives a parse error on line 6 unexpected TOKEN:select and in xBasic where I need it so thatbthe resultset can be worked on it just fails. can anybody see what is wrong with it?

[Pat Bremkamp]

I've found Alpha to sometimes have a hard time with table names or fields that contain spaces. I suggest you build this as a view with a name with no spaces, and use the "as" to change all fileds that contain spaces to no-space names.

[Lance Gurd]

Thanks Pat, that worked, however it did not give me the desired resultset, so back to the drawing board.

I am trying to replicate the data that is returned from a nested SQL query whereby one header record is returned with the linked data from up to 4 child tables and then turn this into JSON and seem to be hitting a brick wall!

[lgrupido]

If you need more help on this, give me a call. A complex sql query like that is difficult to help fix without seeing it - but I would be willing to take a quick look via a remote session if it would help get you over the hump.

Just message me if you would like to try that. (No charge!)

[Lance Gurd]

I was looping through the data to get this to work for each table , then I discovered a5_sql_nested_query_to_json_document() and with a bit of manipulation of the result got it to work