MySQL join two tables based on a condition

Question

I have two tables.

users

| id | name  |
| ---|-------|
| 1  | Jhon  |
| 2  | David |

swipes

| id | swp_from | swp_to| first_swp| second_swp |
| ---|----------|-------|----------|------------|
| 1  | 1        | 2     | like     | pending    |
| 2  | 1        | 2     | like     | pending    |

I need to join these to tables from both swp_from and swp_to depending upon condition.

My query goes like this:

SELECT name, swp_to, first_swp FROM swipes
LEFT JOIN users ON users.id = swipes.swp_to // JOINING WITH swp_to TO MATCH FIRST WHERE CONDITION
WHERE 
(swipes.swp_from = :me AND ((swipes.first_swp NOT IN ('like', 'superlike')) OR (swipes.first_swp IN ('like', 'superlike') AND swipes.second_swp NOT IN ('like', 'superlike')))) // FIRST CONDITION
OR
(swipes.swp_to = :me AND swpipes.second_swp != 'pending') // FOR THIS SECOND CONDITION I NEED TO JOIN USERS TABLE USING swp_from AS WELL IN THIS SAME QUERY

As explained in the query above I have already joined swp_to with the users table. But I also need to join swp_from for the second condition in WHERE clause with the users table. How can I do that?

In simple terms, I need the query to join with users table using swp_to if the first where condition is satisfied. If the second where condtion (after OR) is satisfied then I want the join to work with swp_from instead of swp_to. This should happen for each row. A PHP conditional logic workaround can also be fine.

Please provide expected output. – Rahul Biswas Sep 15 '21 at 16:55 — Rahul Biswas, Sep 15 '21 at 16:55

score 1 · Accepted Answer · answered Sep 15 '21 at 17:22

1

The conditions should be moved to the ON clause.

In MySql a CASE expression can be used to return the result of Boolean expressions (as 1 or 0 for true or false) like this:

SELECT u.name, s.swp_to, s.first_swp 
FROM swipes s LEFT JOIN users u
ON CASE (s.swp_from = :me AND ((s.first_swp NOT IN ('like', 'superlike')) OR (s.first_swp IN ('like', 'superlike') AND s.second_swp NOT IN ('like', 'superlike'))))
  WHEN 1 THEN u.id = s.swp_to
  WHEN 0 THEN u.id = s.swp_from AND (s.swp_to = :me AND s.second_swp <> 'pending')
END;

answered Sep 15 '21 at 17:22

forpas

160,666
10
38
76

I am using MySQL. Is writing `END` necessary here as I do not see a `START` in the beginning? – Relaxing Music Sep 15 '21 at 17:42
@RelaxingMusic The syntax for a CASE expression is `CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END` – forpas Sep 15 '21 at 17:45
Oh got it. Learned something new today. Thanks buddy :) And yes, after I finish trying and yield a successful result, will accept the answer. Else, will knock you again for an update if stuck. Please be there for the help. – Relaxing Music Sep 15 '21 at 17:47
ran into another problem here https://stackoverflow.com/questions/69198223/mysql-case-function-giving-unexpected-results – Relaxing Music Sep 15 '21 at 18:33

MySQL join two tables based on a condition

1 Answers1

Linked