Remove rows from array based on another array

Question

I'm completely stumped and have tried different solutions I've found on SO but nothing has worked. Here is my problem:

I have two tables with completely different structures. One table is for USERS and the other table is for BLOCKS. The goal is to remove any items from the USERS search that appear in the BLOCKS table.

I've tried achieving this through a SQL query using NOT IN but it's only excluding one of items.

I've also tried array_diff in PHP but I'm still seeing the blocked users in the user search results.

I don't know if the table structures have to match or if my SQL is too complex. Any guidance?

Table Structure

> +----------+---------+-----------+
|username  |zipcode  |birthdate  |
+----------+---------+-----------+
|tester55  |72758    |1999-09-09 |
+----------+---------+-----------+
|tester86  |60608    |1983-05-10 |
+----------+---------+-----------+
|iosuser1  |10011    |1975-12-19 |
+----------+---------+-----------+
|iosuser5  |10011    |1975-12-21 |
+----------+---------+-----------+
|tester150 |10511    |1975-12-21 |
+----------+---------+-----------+


Blocks table
+----------+---------+-----------+
|blocker   |blockeduser
+----------+---------+-----------+
|tester86  |tester55 |          |
+----------+---------+-----------+
|iosuser5  |tester55 |         |
+----------+---------+-----------+
|tester150 |tester55 |         |
+----------+---------+-----------+

Zip Code table
+----------+---------+-----------+
|zipcode   |city
+----------+---------+-----------+
|72758     |Rogers   |          |
+----------+---------+-----------+
|60608     |Chicago  |         |
+----------+---------+-----------+

EDIT: Updated query based on feedback from @TomC

    SELECT
    *
FROM
    (
    SELECT
        zipcodes.zip,
        zipcodes.city,
        zipcodes.state,
        users.id,
        users.username,
        users.ava,
        users.gender,
        users.race,
        users.headline,
        users.marital,
        users.height,
        users.bodytype,
        users.religion,
        users.education,
        users.occupation,
        users.politics,
        users.kids,
        users.wantkids,
        users.favdrink,
        users.drink,
        users.smoke,
        users.interests,
        users.aboutme,
        users.seekingGender,
        users.seekingdistance,
        users.seekingrace,
        users.seekingmarital,
        users.seekingminage,
        users.seekingmaxage,
        users.seekingminheight,
        users.seekingmaxheight,
        users.seekingbodytype,
        users.seekingreligion,
        users.seekingeducation,
        users.seekingoccupation,
        users.seekingpolitics,
        users.seekingkids,
        users.seekingwantkids,
        users.seekingdrink,
        users.seekingsmoke,
        users.birthdate,
        YEAR(CURRENT_TIMESTAMP) - YEAR(users.birthdate) -(
            RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(users.birthdate, 5)
        ) AS age,
        3959 * ACOS(
            COS(RADIANS(zipcodes.latitude)) * COS(RADIANS(center.latitude)) * COS(
                RADIANS(zipcodes.longitude) - RADIANS(center.longitude)
            ) + SIN(RADIANS(zipcodes.latitude)) * SIN(RADIANS(center.latitude))
        ) AS distance
    FROM
        users AS seeker
    JOIN zipcodes AS center
    ON
        center.zip = seeker.zip
    JOIN users ON seeker.zip = users.zip
    JOIN zipcodes ON zipcodes.zip = users.zip
    WHERE
        seeker.username = 'tester55' AND seeker.username <> users.username AND users.gender = seeker.seekingGender AND seeker.gender = users.seekingGender AND users.seekingmarital LIKE seeker.seekingmarital AND users.bodytype LIKE seeker.seekingbodytype AND users.religion LIKE seeker.seekingreligion AND users.education LIKE seeker.seekingeducation AND users.occupation LIKE seeker.seekingoccupation AND users.politics LIKE seeker.seekingpolitics AND users.kids LIKE seeker.seekingkids AND users.wantkids LIKE seeker.seekingwantkids AND users.drink LIKE seeker.seekingdrink AND users.smoke LIKE seeker.seekingsmoke AND users.race LIKE seeker.seekingrace AND seeker.seekingminheight <= users.height AND seeker.seekingmaxheight >= users.height AND users.birthdate >= DATE_SUB(
            NOW(), INTERVAL seeker.seekingmaxage YEAR) AND users.birthdate <= DATE_SUB(
                NOW(), INTERVAL seeker.seekingminage YEAR) AND NOT EXISTS(
                SELECT
                    *
                FROM
                    blocks
                WHERE
                    where blocks.blockeduser=seeker.username and blocks.blocker=users.username
            )
            ) selections
        WHERE
            distance < selections.seekingdistance
        ORDER BY
            distance

Answer 1

Your statement that the tables have completely different structures is not necessary, that's the whole idea of a database. You can either use the left join as suggested by @EmaniAzevedo, but you would need to check for both columns as per your requirements, or you would use a not exists.

I prefer the not exists as I think its clearer.

select * from users 
where not exists(
    select * from blocks where Users.username = Blocks.blocker or users.username=Blocks.blockeduser
)

EDIT: as you have now added you query, this not exists should simply go as an additional clause, with an AND to add the clause. I also dont see why you are using a HAVING, I think that should also just be an AND.

Second EDIT: This is your whole query with the confusing brackets removed. It now starts with the seeking user, finds all other users in the same zip, then find all other matching users. Can do this with aliased user tables instead of having to use your nested query.

BTW it checks for the user being on either side of the blocker table - if thats not right modify accordingly.

select * from (
SELECT  zipcodes.zip, zipcodes.city, zipcodes.state,
        users.id, users.username, users.ava, users.gender, users.race, users.headline, users.marital, users.height, users.bodytype, users.religion, users.education, 
        users.occupation, users.politics, users.kids, users.wantkids, users.favdrink, users.drink, users.smoke, users.interests, users.aboutme, users.seekingGender, 
        users.seekingdistance, users.seekingrace, users.seekingmarital, users.seekingminage, users.seekingmaxage, users.seekingminheight, users.seekingmaxheight, 
        users.seekingbodytype, users.seekingreligion, users.seekingeducation, users.seekingoccupation, users.seekingpolitics, users.seekingkids, users.seekingwantkids, 
        users.seekingdrink, users.seekingsmoke, users.birthdate, 
        YEAR(CURRENT_TIMESTAMP) - YEAR(users.birthdate) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(users.birthdate, 5)) as age, 
        3959 * acos(cos(radians(zipcodes.latitude)) *
                cos(radians(center.latitude)) *
                cos(radians(zipcodes.longitude ) -
                    radians(center.longitude)) +
                sin(radians(zipcodes.latitude)) *
                sin(radians(center.latitude))) AS distance 
from users as seeker
join zipcodes as centre on centre.zip=seeker.zip
JOIN users ON seeker.zip = users.zip
join zipcodes on zipcodes.zip=users.zip
where seeker.username = 'tester55' and seeker.username<>users.username AND users.gender = seeker.seekingGender AND seeker.gender=users.seekingGender
    AND users.seekingmarital LIKE seeker.seekingmarital AND users.bodytype LIKE seeker.seekingbodytype AND users.religion LIKE seeker.seekingreligion 
    AND users.education LIKE seeker.seekingducation and users.occupation LIKE seeker.seekingoccupation AND users.politics LIKE seeker.seekingpolitics 
    AND users.kids LIKE seeker.seekingkids AND users.wantkids LIKE seeker.seekingwantkids AND users.drink LIKE seeker.seekingdrinker 
    AND users.smoke LIKE seeker.seekingsmoker AND users.race LIKE seeker.seekingrace AND seeker.seekingminheight <= users.height AND seeker.seekingmaxheight >= users.height 
    AND users.birthdate >= DATE_SUB(NOW(), INTERVAL seeker.seekingmaxage YEAR) AND users.birthdate <= DATE_SUB(NOW(), INTERVAL seeker.seekingminage YEAR)
    and not exists(
        select * from blocks where blocks.blocker=users.username and blocks.blockeduser=seeker.username
    )
) selections
where distance < selections.seekingdistance
ORDER BY distance

EDIT: A really simplifed version of the above, using your original sample data, including the table defs and inserts. I updated all your sample users to be the same zip, and used that as the only criteria. If this isnt working for you make sure your blocked users actually match properly - no trailing spaces for example.

create table users (username varchar(20),zipcode varchar(10),birthdate date)
insert users values ('tester55','10011','1999-09-09')
,('tester86','10011','1983-05-10')
,('iosuser1','10011','1975-12-19')
,('iosuser5','10011','1975-12-21')
,('tester150','10011','1975-12-21')

create table Blocks(blocker varchar(20),blockeduser varchar(20))
insert Blocks values ('tester86','tester55'),('iosuser5','tester55'),('tester150','tester55')

create table ZipCode(zipcode varchar(10), city varchar(20))
insert zipcode values ('72758','Rogers'),('60608','Chicago')

select users.*
from users seeker
join users on users.zipcode=seeker.zipcode and users.username<>seeker.username
where seeker.username='tester55'
and not exists(select * from blocks where blocks.blocker=users.username and blocks.blockeduser=seeker.username)

Result: (All the users who blocked tester55 have been excluded)

username    zipcode birthdate
iosuser1    10011   1975-12-19

Answer 2

Presuming this structure are correct:

MariaDB [test]> SELECT * FROM `Users`;
+----------+---------+------------+
| username | zipcode | birthdate  |
+----------+---------+------------+
| tester55 |   72758 | 1999-09-09 |
| tester86 |   60608 | 1983-05-10 |
| iosuser1 |   10011 | 1975-12-19 |
| iosuser5 |   10011 | 1975-12-21 |
+----------+---------+------------+
4 rows in set (0.00 sec)

and

MariaDB [test]> SELECT * FROM `Blocks`;
+----------+-------------+
| blocker  | blockeduser |
+----------+-------------+
| tester86 | tester55    |
| iosuser5 | tester55    |
+----------+-------------+
2 rows in set (0.00 sec)

You can use the follow query:

MariaDB [test]> SELECT `Users`.* FROM `Users` LEFT JOIN `Blocks` ON `Users`.`username` = `Blocks`.`blocker` WHERE `Blocks`.`blocker` IS NULL;
+----------+---------+------------+
| username | zipcode | birthdate  |
+----------+---------+------------+
| tester55 |   72758 | 1999-09-09 |
| iosuser1 |   10011 | 1975-12-19 |
+----------+---------+------------+
2 rows in set (0.00 sec)