complex query returns too many results

[PPS]

I have a class registration form. The database has a semester table, class table, student table and registration table.
I want a query to return a list of students that are registered for a class in an active semester. I have the query, but it returns each student twice. I can use DISTINCT, but I feel that using it is necessary because I have a poorly formed query. Or, maybe I do need to use DISTINCT??
Can someone please take a look and suggest how I can improve my results?

SELECT
c.student_id,
c.student_last_name ||', '|| c.student_first_name As "student_name", 
c.student_esis_id AS "esis_id",
FROM 
(
summer_scholars_semester a
INNER JOIN 
summer_scholars_class b 
ON
a.semester_id = b.semester_id
),
 (
summer_scholars_registration r
INNER JOIN
summer_scholars_student c
ON
c.student_id = r.student_id
)         
WHERE         
b.class_id = r.class_id
AND a.active
AND a.semester_deleted_date IS NULL
AND c.student_deleted_date IS NULL               
ORDER BY 
"student_name"

[BitBlt]

It looks like you're mixing and matching your join syntax...always a bad idea, possibly creating a weird cross join situation.
You should be using something like:

...
FROM summer_scholars_semester a
INNER JOIN summer_scholars_class b 
ON a.semester_id = b.semester_id
INNER JOIN summer_scholars_registration r
on b.class_id = r.class_id
INNER JOIN summer_scholars_student c
ON c.student_id = r.student_id
WHERE
...

Not sure if this will solve your problem because we don't have your table structures, with keys, FKs, etc. Hopefully this hint will be enough. Good luck!

[PPS]

Thank you, that has made for a much easier to read SQL. Looking at it in this better design makes me think that it is a candidate for 'DISTINCT', simply because one student can register for multiple classes. There is a reason 'DISTINCT' exists, so hopefully it should be in this code for good reason?

I have altered the SQL to:

SELECT
s.student_id,
s.student_last_name ||', '|| s.student_first_name As "student_name", 
s.student_esis_id AS "esis_id",
s.student_school_name
FROM summer_scholars_student s
INNER JOIN summer_scholars_registration r
ON s.student_id = r.student_id
INNER JOIN summer_scholars_class c
ON c.class_id = r.class_id
INNER JOIN summer_scholars_semester e
ON c.semester_id = e.semester_id        
WHERE e.active
AND e.semester_deleted_date IS NULL
AND s.student_deleted_date IS NULL               
ORDER BY 
"student_name"

Still get each student twice if they are registered for 2 classes.
Here are the tables

CREATE TABLE summer_scholars_semester (
    semester_id serial NOT NULL PK,
    semester_begin_date date,
    semester_end_date date,
    semester_reg_deadline date,
    semester_title character varying(100),
    semester_created_date timestamp without time zone DEFAULT now(),
    semester_created_user_id character varying(20),
    semester_updated_date timestamp without time zone,
    semester_updated_user_id character varying(20),
    semester_deleted_date timestamp without time zone,
    active boolean DEFAULT true
);

CREATE TABLE summer_scholars_class (
    semester_id integer FK to semester,
    class_id serial NOT NULL PK,
    class_title character varying(50),
    class_code character varying(20),
    class_students_limit integer DEFAULT 0,
    class_created_date timestamp without time zone DEFAULT now(),
    class_created_user_id character varying(20),
    class_updated_date timestamp without time zone,
    class_updated_user_id character varying(20),
    class_deleted_date timestamp without time zone,
    class_min_grade_level character varying(20) DEFAULT 9
);\

CREATE TABLE summer_scholars_student (
    student_id serial NOT NULL PK,
    student_esis_id character varying(10),
    student_first_name character varying(30),
    student_middle_name character varying(30),
    student_last_name character varying(30),
    student_grade_level character varying(10),
    student_school_location_id integer,
    student_school_name character varying(100),
    student_counselor_name character varying(100),
    student_in_pps boolean,
    student_address character varying(100),
    student_city character varying(100),
    student_state character varying(2),
    student_zip character varying(20),
    student_apt_no character varying(10),
    student_created_date timestamp without time zone DEFAULT now(),
    student_created_user_id character varying(20),
    student_updated_date timestamp without time zone,
    student_updated_user_id character varying(20),
    student_deleted_reason character varying(200),
    student_deleted_date timestamp without time zone
);

CREATE TABLE summer_scholars_registration (
    registration_id serial NOT NULL  PK,
    class_id integer,
    student_id integer FK to student,
    fee_id integer DEFAULT 0,
    student_noshow boolean DEFAULT false,
    fee_type character varying(100),
    fee_type_verified boolean DEFAULT false,
    class_fee numeric DEFAULT 0.00,
    registration_paid_cash numeric DEFAULT 0.00,
    registration_paid_credit_card numeric DEFAULT 0.00,
    registration_paid_check numeric DEFAULT 0.00,
    registration_check_number character varying(100),
    registration_payment_agency character varying(100),
    registration_payment_date timestamp without time zone,
    registration_payment_user_id character varying(20),
    registration_date timestamp without time zone DEFAULT now(),
    registration_user_id character varying(20),
    registration_updated_date timestamp without time zone,
    registration_updated_user_id character varying(20),
    registration_deleted_date timestamp without time zone,
    registration_deleted_reason character varying(200),
    registration_complete boolean DEFAULT false
);

[BitBlt]

You could try something like this:

select
s.student_id,
s.student_last_name + ', ' + s.student_first_name As 'student_name', 
s.student_esis_id AS 'esis_id',
s.student_school_name
from dbo.summer_scholars_student s
where s.student_deleted_date IS NULL               
and exists
(
SELECT 1
FROM dbo.summer_scholars_registration r
INNER JOIN dbo.summer_scholars_class c
ON c.class_id = r.class_id
INNER JOIN dbo.summer_scholars_semester e
ON c.semester_id = e.semester_id        
WHERE s.student_id = r.student_id
and e.active
AND e.semester_deleted_date IS NULL
)
ORDER BY 
'student_name'

[PPS]

Oy, I hit resend. Sorry.
Thank you BitBlt, that works perfectly.