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.
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)
0 comments:
Post a Comment