CODEWITHSUNDEEP
mysqlsql
Ricky Sharma
Ricky Sharma

Reputation: 1919

mysql join not returning the expected ids

I have two table. I want to join them.

This table is "program_participants"

program_participants

This table is "logsesion"

enter image description here

My query 

    SELECT a.`id_participant` FROM `program_participants` a
INNER JOIN `logsesion` b
on a.`id_participant` != b.`user_id`
GROUP BY a.`id_participant`

Now after running the query above I get a.id_participant (1 to 9 means all of it from participants table) But I want all of it except 1 and 2 as they are present in the logsesion table. can you please tell me what I am doing wrong. I have spend so much time on this and this seems to be straight forward. I have also tried symbol <> as well.

Upvotes: 0

Views: 32

Answers (3)

stackMonk
stackMonk

Reputation: 1033

If you really want to join these table you can try this, this will give the cartesian product of these tables filtering the userids already in the logsesion table. If you want different results comment below:

SELECT pa.`id_participant`
FROM `program_participants` pa JOIN
     `logsesion` ls
WHERE pa.`id_participant` NOT IN 
(
 SELECT user_id from `logsesion`
);

Upvotes: 0

Michael
Michael

Reputation: 2942

I'm not sure but something like this shoul work:

SELECT a.`id_participant` FROM `program_participants` a
LEFT JOIN `logsesion` b
ON a.`id_participant` = b.`user_id`
GROUP BY a.`id_participant`
HAVING b.`user_id` IS NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want a left join and then a comparison to filter out the records that match. The ones that remain have no match:

SELECT pa.`id_participant`
FROM `program_participants` pa LEFT JOIN
     `logsesion` ls
     ON pa.`id_participant` = ls.`user_id`
WHERE ls.user_id is null;

Upvotes: 1

Related Questions