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"

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!

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
);

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'

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.