"mysql join with not equal to operator " not working

Question

first attempt

SELECT s.name,s.email,s.student_id,a.hostel_id,s.image,s.course_id,s.year, s.address
FROM sys_student_account s, sys_room_allotment a
WHERE s.student_id!=a.student_id AND s.name like 'SAMS%'
GROUP BY s.student_id ORDER BY s.name LIMIT 0,20

second attempt

SELECT s.name,s.email,s.student_id,a.hostel_id,s.image,s.course_id,s.year, s.address
FROM sys_student_account s, sys_room_allotment a
WHERE NOT(s.student_id=a.student_id) AND s.name like 'SAMS%'
GROUP BY s.student_id ORDER BY s.name LIMIT 0,20

can anyone help me please?

What exactly is the problem? Are you getting an error? The wrong result? — Mureinik
– Mureinik, Commented Aug 29, 2015 at 14:09
no no i am not getting error.i just want to get info of student from student account table which are not existing in room allotment table, but the query not working properly — Sameer Sharma
– Sameer Sharma, Commented Aug 29, 2015 at 14:12
Then edit the question to explain what you want, and what you get instead. — Tilman Hausherr
– Tilman Hausherr, Commented Aug 29, 2015 at 14:35

seahawk · Accepted Answer · 2015-08-29 14:26:18Z

1

You need to understand joins properly. Your code gives entirely different result than what you want.

use following code snippet:

from sys_student_account s
left join sys_room_allotment a on ( s.student_id=a.student_id)

where a.student_id is NULL and s.name like 'SAMS%'

Note: check whether you need group by clause and implement accordingly.

answered Aug 29, 2015 at 14:26

seahawk

1,92213 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2015-08-29 14:35:02Z

A join, be it an implicit or explicit join creates a Cartesian product between both tables - that it, it pairs every row from the first table with every row from the other table. This result is narrowed down by the join condition which should ensure that these matches are done according to some logic according to your schema. In this case, you are attempting to join a row in sys_student_account with every row in sys_room_allotment that has a different student_id - which isn't really what you're trying to achieve. A much simpler approach may be to use the exists operator instead of a join:

SELECT   s.* -- or any other fields from sys_student_account
FROM     sys_student_account s 
WHERE    NOT EXISTS (SELECT *
                     FROM   sys_room_allotment a
                     WHERE  s.student_id = a.student_id)
ORDER BY s.name

Collectives™ on Stack Overflow

"mysql join with not equal to operator " not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related