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

User table is called Users. Based on the data sets iosuser1 should be returned. Would it be helpful if I posted my query. It's a lengthy query that searches based on user preferences and distance. It excludes the user performing the search from the results and checks to see if they've been blocked by anyone. — techgirl
– techgirl, Commented Oct 14, 2018 at 23:15
Looks like it should only remove one of the items. Are blocker and blockeduser reversed on the table ? — msg
– msg, Commented Oct 14, 2018 at 23:15
@msg It should return iosuser1 based on the example. Blocker and blockeduser aren't reversed. — techgirl
– techgirl, Commented Oct 14, 2018 at 23:17

TomC · Accepted Answer · 2018-10-16 01:41:06Z

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

Ernani Azevedo · Accepted Answer · 2018-10-14 23:21:22Z

0

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)

answered Oct 14, 2018 at 23:21

Ernani Azevedo

4612 silver badges6 bronze badges

2 Comments

techgirl Over a year ago

HI @Ernani, I tried to incorporate your solution into my query. But am getting errors. When I tried your query alone it worked great. Would it help if I posted my full query. I really appreciate your help.

techgirl Over a year ago

I've added in my query. It's lengthy and not the best so please go easy on me.

Collectives™ on Stack Overflow

Remove rows from array based on another array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related