complex sql statement, need help

i have a complex sql statement and i think that my structure looks good but apparently not because i keep getting the same error, i was wondering if anyone knew how to correct this problem.

SELECT A.*, B.Name, C.SIName, D.IID,

(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID , A
WHERE F.Emp='Service' AND E.Lead=1 AND E.ID=[A].[D])
AS Service,

(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID ,A
WHERE F.Emp='Industry' AND E.Lead=1 AND E.ID=[A].[D])
AS Industry

FROM A , B, C
WHERE (1=1) AND B.SID = C.SID AND A.ID = B.ID

i always get this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

does anyone know how to fix this query?Whats this syntax?

FROM E INNER JOIN F ON E.SID =
F.SID , A|||im not entirely sure, im in the process of converting this from access to sql server. i would assume that they are going ahead and assigning the table from which they will be calling on later. i usually dont use the inner join method myself, i will just type what fields i want, then the tables and then the criteria, so im guessing its along the same lines by calling table A and then specifying criteria on the next line
AND E.ID=[A].[D])|||You are getting the error because the subqueries in your select clause return more than one value (LastName and FirstName), and you are trying to assign the results to a single field in your result set (Service).

You can probably avoid the error by concatenating the two values into one:
[LastName] + ' ' + [FirstName]

but this is not a good way to write a sql statement. There are a lot of problems with the code you posted. We can probably give you some help cleaning it up, but you will need to tell us a bit more about what you want to do, and post a description of the tables and their columns.|||Are the tables really called A, B, C?

Can you post the DDL for these tables?

Do you know what the expected result is suppose to be?

I'm guessing they're trying to coorelate to table A for the names...I think I'd make it just part of the join...with a derived table

Alos you should start using the JOIN syntax...it'll be easier for you in the long run

(My Own Opinion)

MOO|||thanks for the info guys, i think i got it fixed, i used blindmans suggestion and it worked. also ill keep your advice in mind brett|||only problem i have now, is it returns each record 15 times|||This is just a guess, and it would really help if we had the DDL for tables A,B,C,E and F (are the tables really called this?) along with some sample data...

But here's a shot

SELECT A.*
, B.Name
, C.SIName
, D.IID
, N1.*
, N2.*
FROM B
INNER JOIN C ON B.ID = C.ID
INNER JOIN A ON B.ID = A.ID
INNER JOIN ( SELECT [LastName] , [FirstName] FROM E
INNER JOIN F ON E.SID = F.SID
WHERE F.Emp='Service' AND E.Lead=1) AS N1
ON N1.ID=A.D
INNER JOIN ( SELECT [LastName] , [FirstName] FROM E
INNER JOIN F ON E.SID = F.SID
WHERE F.Emp='Industry' AND E.Lead=1) AS N2
ON N2.ID=A.D|||You can avoid the duplicated records by using the SELECT DISTINCT syntax, but this and the other suggestion I gave you are really just band-aid solutions for more serious problems.
For instance, your subqueries appear to use cartesian joins to tables that are not reference in the select list or the criteria, and your subqueries in SELECT clauses are not a good idea to begin with. They should be dropped in favor of direct joins or moved to the FROM clause.|||yea i know it would help, but i got it figured out now. i dont have a lot of experience with converting from access to sql and i assumed that a lot of complex changes were neccessary when come to find out, there was really only a small change to convert it. the code i showed you guys obviously included my f'ed up alterations. but the only problem was the &'s from access had to be switched to +'s in sql. so sorry for taking up your time guys.