SQL Syntax headache

[Robert Masterson]

I have run into a small issue with the syntax for use with Mysql vs SQL Server:
the following works on SQL Server but not with MySQL

select ID, DateEntered, name, (select Company from contacts
where ID = c.DiveShopRef) as myCompany from contacts c ORDER by ID ASC

and produces:
ID Date Entered name myCompany
-------------------- --------------------------- ---------------------------------------- --------------------------------------------------

I understand that the sub-query is producing the derived-column [MyCompany], but cant seem to get the right syntax for this to work with Mysql/MyODBC 3.51

I tried it as a UNION as well but I keep getting syntax errors -

I know should be an easy one for somebody who does SQL all day... Any sugestions would be welcome

Many Thanks
bobm