时间戳

首页 > 解决方案 > MySQL Query to get all neighborhoods which a user did not join

mysql - MySQL Query to get all neighborhoods which a user did not join

问题描述

I want to get all the neighborhoods (based on different zips) which the user is not a member of already.

I have a users table and several other tables like this:

table name: neighborhood

CREATE TABLE neighborhood(
    `neighborhood_id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `neighborhood_postal_code` VARCHAR(255) NOT NULL,
    `region_neighborhood` VARCHAR(255) NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`neighborhood_id`),
    INDEX `neighborhood_region_neighborhood_FI_1` (`region_neighborhood`)
) ENGINE = InnoDB;

table name: user_neighborhood

CREATE TABLE user_neighborhood(
    `user_id` INT(11) NOT NULL,
    `neighborhood_id` INT(11) NOT NULL,
    `activity_circle` INT(1) DEFAULT 0,
    `duo_circle` INT(1) DEFAULT 0,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
    FOREIGN KEY (`neighborhood_id`) REFERENCES `neighborhood` (`neighborhood_id`)
) ENGINE = InnoDB;

I have tried the following query, but the result is not correct:

SELECT n.*
FROM `neighborhood` as n
left join user_neighborhood as un on n.neighborhood_id = un.neighborhood_id
where un.user_id != 1 and n.neighborhood_postal_code IN ('2000', '2100')

UPDATE: I managed to make the query seem correct at first instance using a subquery like this:

select *
from neighborhood
where neighborhood_id NOT IN (select neighborhood_id from user_neighborhood where user_id != 1)
AND neighborhood_postal_code IN ('2000', '2100')

However, it also returns (some) of the neighborhoods i am in already. It doesnot make much sense to me why only some..

标签： mysql

解决方案

Why exactly are you adding user_id != 1 in your subquery? I think if you know the id of the user you want to fetch for lets say user_id is 10 then use where user_id = 10 in subquery like:

select *
from neighborhood
where neighborhood_id NOT IN (select distinct neighborhood_id from user_neighborhood where user_id = 10)
AND neighborhood_postal_code IN ('2000', '2100')

But if you want to fetch all the neighbors which have no user then you can use this Query:

select *
from neighborhood
where neighborhood_id NOT IN (select distinct neighborhood_id from user_neighborhood)
AND neighborhood_postal_code IN ('2000', '2100')

Hope this helps!

推荐阅读