CODEWITHSUNDEEP
mysqlsqlselectjoinleft-join
user2987001
user2987001

Reputation: 65

INNER JOIN does not work for me

I'm trying to make my SQL query work and I fail, so I decided to ask more experienced and familiar with SQL people since I'm not so.

What I have: 2 tables in my DB, one is "DEV" table that contains: id, lat, lon, login, password second one is "TASK" table which contains: id, lat, lon, address, id_dev. Id_dev is a foreign key to table "DEV".

What I'm trying to do is: Make query to get all DEVs that have NO task assigned (there is no record in table "task" with given dev.id) and get another list of DEVs that have tasks.

I want them separated. I tried something from a tutorial:

SELECT * FROM `dev` INNER JOIN 'task' ON dev.id=task.id_dev ORDER BY dev.id;

But it didn't work for me. Any suggestions please? Kind regards!

Upvotes: 0

Views: 117

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Use LEFT JOIN instead of INNER JOIN.

Try this: 

SELECT d.* 
FROM `dev` d 
LEFT JOIN `task` t ON d.id = t.id_dev 
WHERE t.id IS NULL 
ORDER BY d.id;

Upvotes: 0

Nikola
Nikola

Reputation: 2132

SELECT * FROM `dev` INNER JOIN 'task' ON dev.id=task.id_dev ORDER BY dev.id;

Do not use single quote ' on task. Query should look like:

SELECT * FROM `dev` INNER JOIN `task` ON dev.id=task.id_dev ORDER BY dev.id;

Upvotes: 0

Rowland Shaw
Rowland Shaw

Reputation: 38130

If you want the 'dev' records with no 'task' you shouldn't use INNER JOIN as that brings back the intersection of the sets.

One option is to use a LEFT JOIN, so something like:

SELECT      dev.* 

FROM        dev

LEFT JOIN   task
    ON      dev.id=task.id_dev 

WHERE       task.id_dev IS NULL

ORDER BY    dev.id;

Upvotes: 6

Related Questions