|
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,order,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!
| 
