SQL Joins Help

[ianhaney]

Hi

I am having some trouble with SQL JOINS as always got bit confused by them and if to use JOIN, LEFT JOIN etc

I am trying to get id, ref, role from jobs table and id, name from employers table cause employers add jobs from a form into a db and I want to display the jobs they add on their profile page so they can edit the job advert or delete the job advert once filled

Hope that makes sense

Below is SQL query I have so far



SELECT j.id, j.ref, j.role, j.division, j.location, j.salary, j.description FROM jobs AS j LEFT JOIN `employees` AS e.id = j.id


I get the following error though on the employer-profile.php page

Warning: mysqli::query(): (42000/1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id = j.id' at line 1 in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/security-site/employer-profile.php on line 60 Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id = j.id' at line 1

Thank you in advance

Ian

[ianhaney]

Sorry a quick update, I think I have figured out bit more, if I am right the same id column had to be in the other table as well so I added memberID to the jobs table so it matches up with memberID that is in the employers table

I still cant work out the JOINS though, sorry

[ianhaney]

Think I just sussed it, is working perfect now

just a last little question

How do I get the memberID added into jobs table as well when they add a job that adds memberID into the employers table?

[ianhaney]

Thought I sussed it until I logged in as another employer which is memberID 12 and the two jobs posted from memberID 5 are showing

Do I need a where clause in the SQL query

would it be something like WHERE memberID = 'memberID';

[ianhaney]

I got is working in phpmyadmin using the following if I enter the memberID manually i.e. 5 or 12 but would it be WHERE employers.memberID = 'employers.memberID';

SELECT employers.memberID, jobs.id, jobs.ref, jobs.role, jobs.division, jobs.location, jobs.salary, jobs.description, jobs.datecreated, jobs.dateupdated
FROM employers
INNER JOIN jobs ON employers.memberID = jobs.memberID
WHERE employers.memberID = '5';

[ianhaney]

Think I sorted it, it is working perfect now, on employers profile page, I have the following SQL query just in case it helps others

I added the following line above the SQL SELECT query



$id = $_SESSION["memberID"]; // store the user id into session


then my SQL query line looks like the following



// get the records from the database
if ($result = $mysqli->query("SELECT employers.memberID, jobs.id, jobs.ref, jobs.role, jobs.division, jobs.location, jobs.salary, jobs.description, jobs.datecreated, jobs.dateupdated
FROM employers
INNER JOIN jobs ON employers.memberID = jobs.memberID WHERE employers.memberID='$id'"))