MySQL Inner Join with condition for the same table

Question

I have a table named activities which includes activities of different kinds, some of them are related. Each activity of type A is matched with an activity of type B, for simplicity let's assume there is no missing data. Up to this point the setup is very similar to this question.

Each activity has a few columns, including activity_type, related_activity_id and location_id. I can query for the pairs very easily:

SELECT 
A.location_id as 'A location id',
B.location_id as 'B location id'
FROM (SELECT * FROM activities WHERE `activities`.`activity_type` IN ('Activities::A') AND (`activities`.`related_activity_id` IS NOT NULL)) AS A
INNER JOIN (SELECT * FROM activities WHERE `activities`.`activity_type` IN ('Activities::B') AND (`activities`.`related_activity_id` IS NOT NULL)) AS B
ON A.related_act_id = B.id;

The problem is when I want to find the mismatching pairs, that is the pairs where the location of A does not match the location of B. I have tried using

WHERE A_location_id <> B_location_id

but then it does not recognize the columns:

Error Code: 1054. Unknown column 'A_location_id' in 'where clause' 0.734 sec

Unwillingly, I have converted the where clause to:

WHERE A.location_id <> B.location_id

But now the query is awfully long and MySQL workbench suffocates and the eventually kills the query:

Error Code: 2013. Lost connection to MySQL server during query

I have tried using a longer timeout query, it did not help even by a bit. Any ideas on how to tacke this?

score 1 · Accepted Answer · edited Jul 05 '21 at 11:14

You can use HAVING in MySQL

BUT SELECT * is bad From as you don't need all columns

    SELECT 
        A.location_id AS 'A location id',
        B.location_id AS 'B location id'
    FROM
        (SELECT 
            *
        FROM
            activities
        WHERE
            `activities`.`activity_type` IN ('Activities::A')
                AND (`activities`.`related_activity_id` IS NOT NULL)) AS A
            INNER JOIN
        (SELECT 
            *
        FROM
            activities
        WHERE
            `activities`.`activity_type` IN ('Activities::B')
                AND (`activities`.`related_activity_id` IS NOT NULL)) AS B ON A.related_activity_id= B.id
    HAVING A_location_id <> B_location_id;

So i would reduce the query to, MySQL has to handle less data

SELECT 
    A.location_id AS 'A location id',
    B.location_id AS 'B location id'
FROM
    (SELECT 
        location_id,
        related_activity_id
    FROM
        activities
    WHERE
        `activities`.`activity_type` IN ('Activities::A')
            AND (`activities`.`related_activity_id` IS NOT NULL)) AS A
        INNER JOIN
    (SELECT 
        location_id,
        id
    FROM
        activities
    WHERE
        `activities`.`activity_type` IN ('Activities::B')
            AND (`activities`.`related_activity_id` IS NOT NULL)) AS B ON A.related_activity_id = B.id
HAVING A_location_id <> B_location_id;

At last if the query takes still to long increase the wait_timeout

YESSSSSS this was the solution! Thanks a million! – Spätzle Jul 05 '21 at 11:04 — Spätzle, Jul 05 '21 at 11:04

score 0 · Answer 2 · answered Jul 05 '21 at 10:01

for me is not so clear,but maybe you can try like this

SELECT 
A.location_id as 'A location id',
B.location_id as 'B location id'
FROM activities c
INNER JOIN activities parent ON parent.activity_type = c.activity_type
WHERE related_activity_id IS NOT NULL AND c.location_id != p.location_id

MySQL Inner Join with condition for the same table

2 Answers2