Select rows from a table not matched in another table

Question

So I have the following:

A lookup table that has two columns and looks like this for example:

userid     moduleid
  4           4

I also have a users table that has a primary key userid which the lookup table references. The user table has a few users lets say, and looks like this:

userid
  1
  2
  3
  4

In this example, it show that the user with ID 4 has a match with module ID 4. The others are not matched to any moduleid.

I need a query that gets me data from the users table WHERE the moduleid is not 4. In my application, I know the module but I don't know the user. So the query should return the other userids apart from 4, because 4 is already matched with module ID 4.

Is this possible to do?

I took the liberty to change the title of your question as it wasn't very descriptive; I hope I managed to reflect your intent as you meant it. — jpw, Feb 26 '15 at 21:03
possible duplicate of [Mysql: Select rows from a table that are not in another](http://stackoverflow.com/questions/11767565/mysql-select-rows-from-a-table-that-are-not-in-another) — Patrick Q, Feb 26 '15 at 21:12

score 0 · Accepted Answer · answered Feb 26 '15 at 21:00

0

I think I understand your question correctly. You can use a sub-query to cross-check the data between both tables using the NOT IN() function.

The following will select all userid records from the user_tbl table that do not exist in the lookup_tbl table:

SELECT `userid` 
FROM `user_tbl`
WHERE `userid` NOT IN (
    SELECT DISTINCT(`userid`) FROM `lookup_tbl` WHERE moduleid = 4
)

answered Feb 26 '15 at 21:00

BenM

52,573
26
113
168

That does it. Thanks! – Sergiu Tripon Feb 26 '15 at 21:09

score 0 · Answer 2 · answered Feb 26 '15 at 21:01

There are several ways to do this, one pretty intuitive way (in my opinion) is the use an in predicate to exclude the users with moduleid 4 in the lookup table:

SELECT * FROM Users WHERE UserID NOT IN (SELECT UserID FROM Lookup WHERE ModuleID = 4)

There are other ways, with possibly better performance (using a correlated not exists query or a join for instance).

score 0 · Answer 3 · answered Feb 26 '15 at 21:09

One other option is to use a LEFT JOIN so that you can get the values from both tables, even when there is not a match. Then, pick the rows where there is no userid value from the lookup table.

SELECT u.userid
FROM usersTable u
    LEFT JOIN lookupTable lt ON u.userid = lt.userid
WHERE lt.userid IS NULL

score -3 · Answer 4 · answered Feb 26 '15 at 21:03

-3

Are you looking for a query like this?

select userid from yourtablename where moduleid<>4

answered Feb 26 '15 at 21:03

Aman Thakur

159
4
14

Select rows from a table not matched in another table

4 Answers4