Return records where fields are equal to that of another record in same table

1

Hi,

I have a query that goes like this

SELECT s.studentid, firstname,lastname,dp,startyear,sum(credits)

FROM students s, results r, courses c

WHERE s.studentid=r.studentid

AND r.courseid=c.courseid

GROUP BY s.studentid

HAVING (dp, startyear) = (SELECT dp, startyear FROM students WHERE studentid=18);

I need to filter it so that it only returns those that have credits less than student 18. I am stuck now as to how to do that. Any ideas?

Also this one above misses out one record as they have no credits. How could I get it still to display the name etc but the sum to read as 0?

Thanks in advance

2

Hi,

Can you provide table structure and some sample data, and the output you expecting as sample below:-

DECLARE @students TABLE
( studentid int NOT NULL,
firstname varchar(max) not null,
lastname varchar(max) not null);
insert into @students
(studentid,firstname,lastname)
select … please define...
union all
select … please define...

DECLARE @results TABLE
( resultid int NOT NULL,
credits int not null);
insert into @results
(resultid,credits)
select … please define...
union all
select … please define...

DECLARE @courses TABLE
( courseid int NOT NULL,
credits int not null);
insert into @courses
(courseid,credits)
select … please define...
union all
select … please define...

I will help you once got above information. Just few line sample data and expecting result.

Thanks.

Regards,
Micheale

3

Which tables are dp, startyear and credits in? When you use multiple tables in a query, be sure to add the table alias prefix (s./r./c.) to all columns in the query. Especially when posting online, because we know nothing about your tables.

4

sorry... the dp, startyear are in the students table and the credits is in the courses. The tables link with studentid between the student and results table and then the courseid is found in the results and courses tables.

5 
SELECT s.studentid, 
    MIN(s.firstname) AS firstname, MIN(s.lastname) AS lastname, 
    MIN(s.dp) AS dp, MIN(s.startyear) AS startyear,
    ISNULL(SUM(c.credits), 0) AS totalcredits
FROM students s
LEFT OUTER JOIN results r ON s.studentid = r.studentid
LEFT OUTER JOIN courses c ON r.courseid = c.courseid
GROUP BY s.studentid
HAVING SUM(c.credits) IS NULL OR /*keep zero credits*/
    SUM(c.credits) < ISNULL((
    SELECT SUM(c.credits)
    FROM students s
    LEFT OUTER JOIN results r ON s.studentid = r.studentid
    LEFT OUTER JOIN courses c ON r.courseid = c.courseid
    WHERE s.studentid = 18), 9999999)
7

Your code is not SQL Server syntax, looks more like maybe DB2? This is a SQL Server forum, so that is the code style I used.