MySQL Query Select where id does not exist in the JOIN table

Question

I wanna know a single MySQL query for solving this problem:

I have a table that consists of member ids and location ids..

_________________________________________________________
|      |                        |                        |
| ID   |  Member Id             |       Location         |
|______|________________________|________________________|
|      |                        |                        |
| 1    |    2371                |           7            |
|      |                        |                        |
| 2    |    5123                |          10            |
|      |                        |                        |
| 3    |    2371                |           9            |
|      |                        |                        |
| 4    |    5123                |           9            |
|      |                        |                        |
| 5    |     565                |           9            |
|      |                        |                        |
| 6    |    2371                |           5            |
|      |                        |                        |
| 7    |    5123                |           6            |
|      |                        |                        |
|______|________________________|________________________|

Another table consists of all the locations data:

__________________________________________________
|                        |                        |
|  Location Id           |       Location Name    |
|________________________|________________________|
|                        |                        |
|      1                 |           ABC          |
|                        |                        |
|      2                 |           BCD          |
|                        |                        |
|      3                 |           CDE          |
|                        |                        |
|      4                 |           DEF          |
|                        |                        |
|      5                 |           EFG          |
|                        |                        |
|      6                 |           GHI          |
|                        |                        |
|      7                 |           HIJ          |
|                        |                        |
|      8                 |           IJK          |
|                        |                        |
|      9                 |           JKL          |
|________________________|________________________|

I wanna find all location ids from the table where member id 2371 is not present. How can I find this in single query? I know I can do it if I break the query into two pieces.. Like array of all locations.. and array of all locations where member id exist. Then using !in_array function of PHP

Thanks for your time and help in advance.

From which table (name) you want to get location IDs? – KV Prajapati Jan 08 '12 at 10:00 — KV Prajapati, Jan 08 '12 at 10:00

Nanne · Accepted Answer · 2012-01-08T10:41:22.323

1

You can find all locations that member 2371 is in by doing:

SELECT location 
FROM memberlocation
WHERE memberid = 2371

You can find all other locations (the ones you want) by:

SELECT * 
FROM location 
WHERE locationid NOT IN 
    ( SELECT location 
      FROM memberlocation
      WHERE memberid = 2371)

edited Jan 08 '12 at 10:41

answered Jan 08 '12 at 10:07

Nanne

64,065
16
119
163

Hi Nanne, When I try your query... SELECT * FROM s_location WHERE s_location_id IS NOT IN(SELECT s_location_id FROM s_location_member WHERE c_member_id = 2371) it gives me an error: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN ( SELECT s_location_member.s_location_id FROM s_location_member ' at line 4" – ppant Jan 08 '12 at 10:27
It was from the top of my head, I suppose "IS NOT IN" isn't good sql, lemme fix – Nanne Jan 08 '12 at 10:40
Hey Nanne, I found the problem in your QUERY.... If I remove IS from the query.. it returns the correct results.. Thanks!! – ppant Jan 08 '12 at 10:53
Hmm.. I saw it later :) Thanks a tonne!! – ppant Jan 08 '12 at 10:58

score 0 · Answer 2 · answered Jan 08 '12 at 10:05

0

You could use the following join query to filter the results by MemberID:

SELECT l.LocationName FROM MemberLocations AS ml
INNER JOIN Locations AS l ON ml.Location = l.LocationID
WHERE ml.MemberID != 2371

answered Jan 08 '12 at 10:05

Damyan Bogoev

688
4
12

What is the problem? Does it return false results? – Damyan Bogoev Jan 08 '12 at 10:35
Yes it return to me all the location ids – ppant Jan 08 '12 at 10:41
This is incorrect. If member 2371 is in location 1 it will not return that row in the join, but if member 2370 is also in location one it WILL return that row. But you don't want that row, because evil member 2371 is there. – Nanne Jan 08 '12 at 10:44
The query should not return Location record with id 7. Because the corresponding MemberLocation record with member id 2371 will be filtered. – Damyan Bogoev Jan 08 '12 at 10:45
@Nanne: If there is record with MemberID 2370 and LocaltionID 7 in the join table, the join query will retrieve this result. – Damyan Bogoev Jan 08 '12 at 10:49
but unless I'm just really low on caffeine: if both are there (so one with 2730 and one with 2731) location 7 will be (once) returned, rigth? And it shouldn't. – Nanne Jan 08 '12 at 10:52
Yes, it will be returned just once. I think this is the case. – Damyan Bogoev Jan 08 '12 at 10:54

MySQL Query Select where id does not exist in the JOIN table

2 Answers2

Linked