CODEWITHSUNDEEP
mysqlsqljoinexists
Desire
Desire

Reputation: 107

Find records which are not exists in other table

I've this table structure:

-request request_id user_id

-user user_id company_id

-company company_id

I want to select all those records from requests table where user_id=? and no such records where the company id of to users is same.

Upvotes: 0

Views: 50

Answers (1)

Stalinko
Stalinko

Reputation: 3646

This is usually achieved using LEFT JOIN:

SELECT r.*
FROM request r
JOIN user u ON r.user_id = u.user_id 
LEFT JOIN u1 ON u1.user_id != u.user_id AND u1.company_id = u.company_id
LEFT JOIN request r1 ON r1.user_id = u1.user_id
WHERE r1.user_id IS NULL

By "where" we say that we don't want "users with same company, who has at least 1 request"

Upvotes: 1

Related Questions