MySQL Subselect

[nemesisdan]

This may seem simple to most, but for some reason i cannot get this to work!!

I have 2 tables, table_a & table_b

Both tables have a string reference number used to join. table_a will only ever have one occurance of the reference number, where as table_b has multiple records with the same reference reference number, ordered by a column called 'order'.

I am trying to pull out all records in table_a and the corresponding record in table_b with an order of '1'. There will always only be one record with order of '1' in table_b for each record in table_a.

In MSSQL I would do something like:

select a.reference_no, b.order, b.date
from table_a a
left join (select reference_no,or der,date from table_b) b on a.reference_no= b.reference_no
where b.order=1

In MySQL it doesn't work! I am running version 5.0 MySQL server which is supposed to support subqueries, but I keep getting a syntax error when I try to execute it. The syntax error always refers to the subselect query.

Any ideas?

Many thanks!

[blyxx86]

SELECT a.reference_no, b.order, b.date
FROM table_a AS a
LEFT JOIN table_b AS b ON a.reference_no=b.reference_no
WHERE b.order=1

You don't actually need to define the SELECT statement within the join. Unless I am missing what you are trying to do.

:)