How to narrow resultset?

kpeeroo · #1 (**permalink**) 08-20-09, 10:43

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.

r937 · #2 (**permalink**) 08-20-09, 11:04

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

kpeeroo · #3 (**permalink**) 08-20-09, 13:54

"GROUP_CONCAT is not a recognised built-in function name" in Visual Web Developer 2005

gvee · #4 (**permalink**) 08-20-09, 14:09

You're right, its a MySQL function

kpeeroo · #5 (**permalink**) 08-20-09, 15:03

so i cant execute it on VWD? Also how will the resultset be following that query?

r937 · #6 (**permalink**) 08-20-09, 15:48

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

kpeeroo · #7 (**permalink**) 12-15-09, 16:45

Any help on this using Sql Server syntax? Thanks.

r937 · #8 (**permalink**) 12-15-09, 16:58

Quote:

Originally Posted by kpeeroo

Any help on this using Sql Server syntax? Thanks.

why would you want SQL Server syntax if you're running against a MySQL database?

kpeeroo · #9 (**permalink**) 12-15-09, 17:11

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

r937 · #10 (**permalink**) 12-15-09, 18:01

okay, it makes sense now

Concatenating Row Values into 1 Record/String (revisited) - Microsoft SQL Server: Programming FAQ - Tek-Tips

kpeeroo · #11 (**permalink**) 12-16-09, 09:48

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?

mnirwan · #12 (**permalink**) 12-16-09, 10:18

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.

kpeeroo · #13 (**permalink**) 12-16-09, 13:04

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

kpeeroo · #14 (**permalink**) 12-16-09, 15:22

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?

r937 · #15 (**permalink**) 12-16-09, 17:06

Quote:

Originally Posted by kpeeroo

Don't know how to make this work.

replace MY_FUNCTION with MAX

Internet Services

Web Development

Digital Marketing

Consumer Tech