CODEWITHSUNDEEP
mysqlsql
Shashi Kiran
Shashi Kiran

Reputation: 362

Need to display records by joining two tables and the records should be Unique

I have the two tables STUDENT and TaskEffort.

Many students have worked on same tasks. For a particular task the name of the students and effort should be considered.

The STUDENT table contains studentid, firstName and lastName.

The TaskEffort table contains taskid, studentid and Effort

I need to display the taskid, first name, last name, effort, for those who worked on a particular task.

This is one of the queries I tried, but this is not working.

SELECT t.id, s.firstname, s.lastname, t.effort 
FROM taskeffort t 
LEFT OUTER JOIN student s ON t.id = 4 AND s.studentid = t.studentid

Thanks in advance.

Upvotes: 0

Views: 54

Answers (2)

Nir-Z
Nir-Z

Reputation: 869

First the design is not accurate. You said that "Many students must have worked on same task" but your design is 1-many students. It means that each task has only one student. You need to change the design to support you requirements. this relation is many-to-many. Student can be assigned to many tasks and tasks may have been assigned to many students. Add a third table called taskAssignments with columns: student_id,task_id and remove student_id column from TaskEffort. Then run this query:

 SELECT t.id, s.firstname, s.lastname, t.effort 
    FROM TaskEffort t
    LEFT JOIN taskAssignments  ta ON t.id=ta.task_id
    LEFT JOIN student s ON s.studentid = ta.studentid
    WHERE  t.id = 4

Upvotes: 1

cw fei
cw fei

Reputation: 1564

SELECT t.id, s.firstname,s.lastname, t.effort 
FROM taskeffort t 
LEFT OUTER JOIN student s ON s.studentid = t.studentid 
WHERE t.id = 4;

Upvotes: 0

Related Questions