How to write a complex SQL query?

Question

Hi friends I need to write a query for a specific senario but I don't know how. Here's the use case: I have a COURSE table, a FACULTY table, and a COURSE_FACULTY table with the many to many relationship between courses and faculties(1 course can have multiple faculties and 1 faculty can be assigned to multiple courses). IsPrimary= true in COURSE_FACULTY table indicates that the faculty is the primary source for students to contact. I want to execute a query to get a list of all course-info including a contact faculty name, one course per 1 row in the result. If a course has multiple faculties, the row-reduce rule is: if at least one faculty is primary, pick any one of primary faculty. If none of the faculties are primary, pick any one of the correlated faculties.

Table: COURSE
PID   NAME  
1     Math
2     Art
3     Science

Table: FACULTY
PID   NAME
1     Sccot
2     John
3     Emma

Table: COURSE_FACULTY
PID   FK_COURSEID FK_FACULTYID IsPrimary
1     1           1            YES
2     1           2            NO
3     2           3            YES
4     3           2            NO
5     3           3            NO     

Desired Result:
PID   NAME      CONTACT_FACULTY
1     Math      Sccot
2     Art       Emma
3     Science   John
Or this is also correct:
PID   NAME      CONTACT_FACULTY
1     Math      Sccot
2     Art       Emma
3     Science   Emma

I know that I'll eventually need to join COURSE table on COURSE_FACULTY.FK_COURSEID = COURSE.PID but I don't know how to eliminate the extra rows. Thanks in advance if anyone can provide help. The best sql I got:

select COURSE.PID, COURSE.NAME,TABLE1.NAME as Faculty  
FROM COURSE  
join (  
  select COURSE_FACULTY.*, FACULTY.NAME   
  FROM FACULTY   
  join COURSE_FACULTY   
  on FACULTY.PID = COURSE_FACULTY.FK_FACULTYID   
  GROUP BY FK_COURSEID)   
as TABLE1   
on COURSE.PID = TABLE1.FK_COURSEID   
order by COURSE.PID asc;

This gives me the desired result by coincidence and I know that groupby is missing logic on chossing which row can stay.

In case anyone want to play here's some quick table creation and data:

create table COURSE(
PID int,
NAME varchar(255)
);

create table FACULTY(
PID int,
NAME varchar(255)
);

create table COURSE_FACULTY(
PID int,
FK_COURSEID int,
FK_FACULTYID int,
IS_PRIMARY varchar(255),
FOREIGN KEY (FK_COURSEID) REFERENCES COURSE(PID),
FOREIGN KEY (FK_FACULTYID) REFERENCES FACULTY(PID)
);

INSERT INTO COURSE(PID, NAME) VALUES(1, 'Math');
INSERT INTO COURSE(PID, NAME) VALUES(2, 'Art');
INSERT INTO COURSE(PID, NAME) VALUES(3, 'Science');
INSERT INTO FACULTY(PID, NAME) VALUES(1, 'Sccot');
INSERT INTO FACULTY(PID, NAME) VALUES(2, 'John');
INSERT INTO FACULTY(PID, NAME) VALUES(3, 'Emma');
INSERT INTO COURSE_FACULTY(PID, FK_COURSEID, FK_FACULTYID, IS_PRIMARY) VALUES(1, 1, 1, 'YES');
INSERT INTO COURSE_FACULTY(PID, FK_COURSEID, FK_FACULTYID, IS_PRIMARY) VALUES(2, 1, 2, 'NO');
INSERT INTO COURSE_FACULTY(PID, FK_COURSEID, FK_FACULTYID, IS_PRIMARY) VALUES(3, 2, 3, 'YES');
INSERT INTO COURSE_FACULTY(PID, FK_COURSEID, FK_FACULTYID, IS_PRIMARY) VALUES(4, 3, 2, 'NO');
INSERT INTO COURSE_FACULTY(PID, FK_COURSEID, FK_FACULTYID, IS_PRIMARY) VALUES(5, 3, 3, 'NO');

user_1144035 · Answer

You can use a correlated subquery:

select c.*,
       (select f.name
        from faculty f join
             course_faculty cf
             on cf.fk_facultyid = f.pid
        order by cf.isprimary desc
        limit 1
       ) as contact_faculty
from course c;

How to write a complex SQL query?

1 Answers