Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    22

    Unanswered: 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?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Whats this syntax?

    FROM E INNER JOIN F ON E.SID =
    F.SID , A
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    22
    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])

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2003
    Posts
    22
    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

  7. #7
    Join Date
    Oct 2003
    Posts
    22
    only problem i have now, is it returns each record 15 times

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    Code:
        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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Oct 2003
    Posts
    22
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •