Query Help

Results 1 to 2 of 2

Thread: Query Help

  1. #1
    Join Date
    Dec 1969

    Default Query Help

    I have a table with 3 different courses <BR><BR>I&#039;m trying to select the individuals with three different course. <BR><BR>I get results back from a table that has close to six hundred records<BR><BR>My query bring back 6 record, does this query look right:<BR><BR>SELECT distinct(userid), fname, lname, course FROM classes <BR><BR><BR>GROUP BY userid, fname, lname, course <BR> having count(course) &#062;= 3 ORDER BY USERID DESC<BR>

  2. #2
    Join Date
    Dec 1969

    Default Nope...

    &nbsp;<BR>You asked for *DISTINCT* records. You CAN NOT use DISTINCT on ONLY ONE FIELD. It applies to *ALL* fields in the SELECT.<BR><BR>So maybe Joe Jones is taking 3 courses. You will get back:<BR> 731 Joe Jones Biology-101<BR> 731 Joe Jones Ethics-103<BR> 731 Joe Jones Computers-1A<BR><BR>There is NO WAY to "consolidate" those records with DISTINCT, because the COURSE values are each different.<BR><BR>Period.<BR><BR>The best you could do would be<BR> SELECT DISTINCT userid, fname, lname, COUNT(course) As CourseCount<BR> FROM classes<BR> GROUP BY userid, fname, lname<BR> HAVING COUNT(course) &#062;= 3<BR> ORDER BY userid DESC<BR><BR>You can&#039;t have both things. Period.<BR><BR>

Posting Permissions

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