MYSQL syntax using two JOINs in one query

[elfynity1]

Hey everyone, I’m having some difficulty with a certain MYSQL statement. MYSQL is not my strong point so i am not surprised. I really have tried to figure this out and just can’t really understand the syntax and why mine doesn’t work and the examples do.

I have 3 tables: job_assembly, job_names and jobs.
I have to constrict the search by the date on the job_assembly table which should give me only 6 rows of output. I need to also get the mass from the jobs table and the job name from the job_names table.
My code is spewing out about 30 rows, which is definitely wrong. Can anyone show me how to do this correctly:

$sql2 = "
SELECT *
FROM job_assembly
INNER JOIN jobs ON job_assembly.assembly_job_names_id = jobs.job_names_id 
INNER JOIN job_names ON job_names.job_id = jobs.job_names_id
WHERE job_assembly.assemble_date = '2020-05-10'
";

$result2 = mysqli_query($conn, $sql2);

if (mysqli_num_rows($result2) > 0) {
	while($row2 = mysqli_fetch_assoc($result2)) {
		echo $row2['job_id'];
		echo $row2['assemble_date'].'<br />';
	} //while
} //if

[m_hutley]

Well, the query as written is legal.

Show us a row that you believe is ‘definitely wrong’, and tell us why it is wrong?

[elfynity1]

Just realised that I haven’t set up a foreign key for my job_assembly table to link to the jobs table which is why it is not working probably. Let me correct this and try again and get back here.

[elfynity1]

After adding a new column called jobs-id in job_assembly that links to the id column in jobs, I was able to come up with this statement which is now working.

FROM job_assembly
LEFT JOIN job_names	ON job_assembly.assembly_job_names_id = job_names.job_id
INNER JOIN jobs ON job_assembly.jobs_id = jobs.id
WHERE job_assembly.assemble_date = '$link_date'