CODEWITHSUNDEEP
mysqlsql
Mr world wide
Mr world wide

Reputation: 4844

Left join is not working fine

table tbl_students 

Student_pid     name                email
1               waheed               [email protected]
2               fareed               [email protected]

table r_job_invitations

id_job   email
101       [email protected]
101       [email protected]
123       [email protected]
123       [email protected]

table r_job_groups 

student_id   job_id   group_id
1             101       1
2             101       2
1             123       1
2             123       2

From the above 3 tables i am trying get the students with a condition. This my query:

    $studentQuery = $conn->query("SELECT
      s.student_pid,jbi.test_status
       FROM `r_job_groups` jtg 
        LEFT JOIN tbl_students s ON jtg.student_id=s.student_pid 
        LEFT JOIN r_job_invitations jbi ON jbi.email=s.student_email 
         where jtg.group_id=".$group." and job_id=".$jobID );

From above query, for values of

$group = 1 and $jobID = 101

Result is coming like this:

student_pid
1
1
2
2

Actual result should be like this:

student_pid
    1
    2

my issue is i am getting students doble time

As per query, that result should give 2 students but it is resulting 4 students because of job id is not working fine joins.
how can i resolve this issue?

Upvotes: 1

Views: 69

Answers (3)

Mr world wide
Mr world wide

Reputation: 4844

    SELECT jbi.*, s.student_pid,jbi.test_status FROM 
`r_job_groups` jtg LEFT JOIN tbl_students s ON jtg.student_id=s.student_pid
 LEFT JOIN r_job_invitations jbi ON jbi.email=s.student_email
 where jtg.group_id=1 and jtg.job_id=109 and jtg.job_id=jbi.id_job

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

be careful with the use of vars in your select you could be subsject to a sql injection

anyway you could use distinct for avoid duplicated value 

  $studentQuery = $conn->query("SELECT DISCINCT
  s.student_pid
   FROM `r_job_groups` jtg 
    LEFT JOIN tbl_students s ON jtg.student_id=s.student_pid 
    LEFT JOIN r_job_invitations jbi ON jbi.email=s.student_email 
     where jtg.group_id=".$group." and jtg job_id=".$jobID );

or use a distinct dinamic table 

  $studentQuery = $conn->query("SELECT 
  s.student_pid
   FROM `r_job_groups` jtg 
    LEFT JOIN ( select distinct student_pid 
           from tbl_students )  s ON jtg.student_id=s.student_pid 
    LEFT JOIN r_job_invitations jbi ON jbi.email=s.student_email 
     where jtg.group_id=".$group." and jtg.job_id=".$jobID );

And lloking to your data sample try also to change the order of the joi table 

$studentQuery = $conn->query("SELECT DISTINCT s.student_pid
      FROM  tbl_students s  
      LEFT `r_job_groups` jtg  s ON jtg.student_id=s.student_pid 
      LEFT JOIN r_job_invitations jbi ON jbi.email=s.student_email 
     where jtg.group_id=".$group." and job_id=".$jobID );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

The invitations table seems totally unnecessary -- and the cause of your duplicates. Write the query as:

SELECT s.student_pid
FROM `r_job_groups` jtg LEFT JOIN
      tbl_students s
      ON jtg.student_id = s.student_pid 
WHERE jtg.group_id = ".$group." and jtg.job_id = ".$jobID;

I also suspect that you want JOIN and not LEFT JOIN.

Upvotes: 1

Related Questions