Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    Unanswered: Union Sql Type: Error

    ok, this might be complicated,
    but I am trying to Create a union sql statement, that is... one list (select) all records where programlength field should not equal to "academic year"

    and then union with the other sql query that selects all records where programlength field is equal to "academic year" BUT selects only the minimum of the record, which means only select the lowest record of duplicate records by using PIID and Term.

    here is my code.. hopefully you are able to get it

    oh,and the error I get when I run it is the following:
    "You tried to execute a query that does not includethe specified expression 'SID' as part of an aggregate function"
    And SID is unique student ID, which is a primary key as well.

    [SQL]

    SELECT a.PIID, a.SID, tblPermInfo.LastName, tblPermInfo.FirstName, tblPermInfo.Ethnicity, tblPermInfo.Gender, tblPermInfo.LocalEmail, tblPermInfo.AbroadEmail, tblProcessInfo.Host, tblProcessInfo.Sponsor, tblProcessInfo.Programname, tblHostInfo.Country, tblProcessInfo.Country, tblPermInfo.ClassStatus, tblProcessInfo.ProgramType, tblProcessInfo.Term, a.Major, tblPermInfo.USCitizen, a.College, (SELECT Max(b.Major)
    FROM tblMajor AS b
    WHERE b.SID = a.SID
    AND a.MajId < b.MajId) AS second_major, (SELECT Max(d.Minor)
    FROM tblMinor AS d, tblMinor AS e
    WHERE d.SID = a.SID
    AND d.Minor <> e.Minor) AS Minor
    FROM tblMajor AS a, tblProcessInfo, tblPermInfo, tblHostInfo
    WHERE a.MajId = (SELECT Min(c.MajId)
    FROM tblMajor AS c
    WHERE c.SID = a.SID) AND tblProcessInfo.SID = a.SID AND tblHostInfo.HostInstitution = tblProcessInfo.HOST AND tblPermInfo.SID = a.SID AND (tblProcessInfo.Term="AY024-031" Or tblProcessInfo.Term="024" Or tblProcessInfo.Term="031" Or tblProcessInfo.Term="032" Or tblProcessInfo.Term="033") AND USCitizen = 'Yes' AND a.ProgramLength NOT LIKE "Academic Year"
    GROUP BY a.SID, tblPermInfo.LastName, tblPermInfo.FirstName, tblPermInfo.Ethnicity, tblPermInfo.Gender, tblPermInfo.LocalEmail, tblPermInfo.AbroadEmail, tblProcessInfo.Host, tblProcessInfo.Sponsor, tblProcessInfo.Programname, tblHostInfo.Country, tblProcessInfo.Country, tblPermInfo.ClassStatus, tblProcessInfo.ProgramType, tblProcessInfo.Term, a.Major, tblPermInfo.USCitizen, a.College, b.Major

    UNION


    SELECT Min(a.PIID) AS [MIN PIID], a.SID, tblPermInfo.LastName, tblPermInfo.FirstName, tblPermInfo.Ethnicity, tblPermInfo.Gender, tblPermInfo.LocalEmail, tblPermInfo.AbroadEmail, tblProcessInfo.Host, tblProcessInfo.Sponsor, tblProcessInfo.Programname, tblHostInfo.Country, tblProcessInfo.Country, tblPermInfo.ClassStatus, tblProcessInfo.ProgramType, Min(tblProcessInfo.Term) AS [Min Term], a.Major, tblPermInfo.USCitizen, a.College, (SELECT Max(b.Major)
    FROM tblMajor AS b
    WHERE b.SID = a.SID
    AND a.MajId < b.MajId) AS second_major, (SELECT Max(d.Minor)
    FROM tblMinor AS d, tblMinor AS e
    WHERE d.SID = a.SID
    AND d.Minor <> e.Minor) AS Minor
    FROM tblMajor AS a, tblProcessInfo, tblPermInfo, tblHostInfo
    WHERE a.MajId = (SELECT Min(c.MajId)
    FROM tblMajor AS c
    WHERE c.SID = a.SID) AND tblProcessInfo.SID = a.SID AND tblHostInfo.HostInstitution = tblProcessInfo.HOST AND tblPermInfo.SID = a.SID AND (tblProcessInfo.Term="AY024-031" Or tblProcessInfo.Term="024" Or tblProcessInfo.Term="031" Or tblProcessInfo.Term="032" Or tblProcessInfo.Term="033") AND USCitizen = 'Yes' AND a.ProgramLength = "Academic Year"
    GROUP BY a.SID, tblPermInfo.LastName, tblPermInfo.FirstName, tblPermInfo.Ethnicity, tblPermInfo.Gender, tblPermInfo.LocalEmail, tblPermInfo.AbroadEmail, tblProcessInfo.Host, tblProcessInfo.Sponsor, tblProcessInfo.Programname, tblHostInfo.Country, tblProcessInfo.Country, tblPermInfo.ClassStatus, tblProcessInfo.ProgramType, tblProcessInfo.Term, a.Major, tblPermInfo.USCitizen, a.College, b.Major
    [/SQL]
    Last edited by kicker22; 08-27-04 at 16:48. Reason: :)

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    To make a complicated query simple, I'd suggest you to test both of the queries separately, and when you're sure they work fine, use UNION.

    Just a question: how would YOU feel if you had to debug such an unstructured query? I bet you'll get a headache to figure out what is wrong about it. Therefore, I'd suggest you to a) write structured queries, b) use "[ code ]" (without spaces) to produce more readable source code here, on the Forum.

    Not much of a help, huh?

  3. #3
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    hmm

    thanks for the advice,
    well, the first sql statement does work, but the second one doesnt work after I added Min to both PIID and Term fields.

    so any clues?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's your second query, reformatted so that you can read it --
    Code:
    SELECT Min(a.PIID) AS [MIN PIID]
         , a.SID
         , tblPermInfo.LastName
         , tblPermInfo.FirstName
         , tblPermInfo.Ethnicity
         , tblPermInfo.Gender
         , tblPermInfo.LocalEmail
         , tblPermInfo.AbroadEmail
         , tblProcessInfo.Host
         , tblProcessInfo.Sponsor
         , tblProcessInfo.Programname
         , tblHostInfo.Country
         , tblProcessInfo.Country
         , tblPermInfo.ClassStatus
         , tblProcessInfo.ProgramType
         , Min(tblProcessInfo.Term) AS [Min Term]
         , a.Major
         , tblPermInfo.USCitizen
         , a.College
         , ( SELECT Max(b.Major)
               FROM tblMajor AS b
              WHERE b.SID = a.SID
                AND a.MajId < b.MajId) AS second_major
         , (SELECT Max(d.Minor)
              FROM tblMinor AS d
                 , tblMinor AS e
             WHERE d.SID = a.SID
               AND d.Minor <> e.Minor) AS Minor
      FROM tblMajor AS a
         , tblProcessInfo
         , tblPermInfo
         , tblHostInfo
     WHERE a.MajId 
         = (SELECT Min(c.MajId)
              FROM tblMajor AS c
             WHERE c.SID = a.SID) 
       AND tblProcessInfo.SID = a.SID 
       AND tblHostInfo.HostInstitution = tblProcessInfo.HOST 
       AND tblPermInfo.SID = a.SID 
       AND (tblProcessInfo.Term="AY024-031" 
         Or tblProcessInfo.Term="024" 
         Or tblProcessInfo.Term="031" 
         Or tblProcessInfo.Term="032" 
         Or tblProcessInfo.Term="033") 
       AND USCitizen = 'Yes' 
       AND a.ProgramLength = "Academic Year"
    GROUP 
        BY a.SID
         , tblPermInfo.LastName
         , tblPermInfo.FirstName
         , tblPermInfo.Ethnicity
         , tblPermInfo.Gender
         , tblPermInfo.LocalEmail
         , tblPermInfo.AbroadEmail
         , tblProcessInfo.Host
         , tblProcessInfo.Sponsor
         , tblProcessInfo.Programname
         , tblHostInfo.Country
         , tblProcessInfo.Country
         , tblPermInfo.ClassStatus
         , tblProcessInfo.ProgramType
         , tblProcessInfo.Term
         , a.Major
         , tblPermInfo.USCitizen
         , a.College
         , b.Major
    you have MIN() on tblProcessInfo.Term in the SELECT, but you left tblProcessInfo.Term in the GROUP BY

    this is not all that's wrong with it, either

    you also have b.Major in the GROUP BY, and there's no b table in the outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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