Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: How to narrow resultset?

    Hi,

    Code:
    SELECT     Student.surname, Courses.name, Phone.number, Email.address
    FROM         Academics INNER JOIN
                          Student ON Academics.studentID = Student.studentID INNER JOIN
                          Courses ON Academics.courseID = Courses.courseID INNER JOIN
                          Email ON Student.studentID = Email.studentID INNER JOIN
                          Phone ON Student.studentID = Phone.studentID
    WHERE     (Academics.academicYear = 2) AND (Student.surname LIKE N'James')
    surname courseName number email
    james BPA 7262650 zai@hotmail.com
    james BPA 7262650 zai@yahoo.com

    how do i go about this so that I don't get repeated data in my resultset? Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Student.surname
         , Courses.name
         , Phone.number
         , GROUP_CONCAT(Email.address) AS email_addresses
      FROM Student
    INNER 
      JOIN Academics  
        ON Academics.studentID = Student.studentID
       AND Academics.academicYear = 2
    INNER 
      JOIN Courses 
        ON Courses.courseID = Academics.courseID
    INNER 
      JOIN Email 
        ON Email.studentID = Student.studentID
    INNER 
      JOIN Phone 
        ON Phone.studentID = Student.studentID
     WHERE Student.surname = 'James'
    GROUP
        BY Student.surname
         , Courses.name
         , Phone.number  
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    "GROUP_CONCAT is not a recognised built-in function name" in Visual Web Developer 2005

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're right, its a MySQL function
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    so i cant execute it on VWD? Also how will the resultset be following that query?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kpeeroo
    Also how will the resultset be following that query?
    you can see this for yourself by running the query right in mysql

    use the mysql query browser, or phpmyadmin, or sqlyog, or heidisql, or navicat, or ...

    ... or if you don't have any of those, use the mysql command line
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2009
    Posts
    168
    Any help on this using Sql Server syntax? Thanks.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kpeeroo View Post
    Any help on this using Sql Server syntax? Thanks.
    why would you want SQL Server syntax if you're running against a MySQL database?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2009
    Posts
    168
    Actually I am using SQL Server 2005 and googling this topic I found no easy way to replace the GROUP_CONCAT() function. Most of the results are saying you have to create your own UDF for this except for one result using the 'for xml path('')' or something but I dont know how to incorporate this in the SQL statement

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  11. #11
    Join Date
    Jul 2009
    Posts
    168
    ok so that means i need to create a function and then using your above sql statement, pass on the email values to that function instead oF GROUP_CONCAT?

  12. #12
    Join Date
    Sep 2009
    Posts
    64
    If you don't care about the email address (only want one) then simply add group by Student.surname, Courses.name, Phone.number to your original query.

  13. #13
    Join Date
    Jul 2009
    Posts
    168
    Thanks for the reply but I think I would like all the email addresses like the resultset set shown, ie all email addresses with other columns so that I don't have to create another query. A single query grouping eveything would be nice if that can be done. So it seems that this UDF would do the job. Will try it and post back. I guess the email addresses would be passed to the UDF as a parameter and the resultset returned like in the article mentioned above. Correct?
    Code:
    SELECT Student.surname
         , Courses.name
         , Phone.number
         , MY_FUNCTION(Email.address) AS email_addresses
      FROM Student
    INNER 
      JOIN Academics  
        ON Academics.studentID = Student.studentID
       AND Academics.academicYear = 2
    INNER 
      JOIN Courses 
        ON Courses.courseID = Academics.courseID
    INNER 
      JOIN Email 
        ON Email.studentID = Student.studentID
    INNER 
      JOIN Phone 
        ON Phone.studentID = Student.studentID
     WHERE Student.surname = 'James'
    GROUP
        BY Student.surname
         , Courses.name
         , Phone.number

  14. #14
    Join Date
    Jul 2009
    Posts
    168
    Don't know how to make this work. Any help on this? What if Crystal Reports is used for generating reports so these TSQL statements will be generated automatically by the IDE right?
    Last edited by kpeeroo; 12-16-09 at 16:34.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kpeeroo View Post
    Don't know how to make this work.
    replace MY_FUNCTION with MAX

    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
  •