0

I have two tables.

users

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

swipes

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

I have the following query:

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

The above query does give expected result but it is displaying same result for the other user as well. I want to restrict displaying the results only for :me user. The user :me is the logged in user for whom the results are being fetched for. The problem here with this query is that though it returns correct expected results for :me user BUT it also returns the same result for the other user as in the query as well. This should not be the case. The other user is either swp_from if :me user is :swp_to OR the other user is swp_to if :me user is swp_from.

EXAMPLE (UPDATE):

Let's say for example, :me is logged in user with id 1 i.e., John. In swipes table first row, 1 is swp_from and 2 is swp_to then I need to display David's (user id 2) data to John. Similarly, in second row, 1 is in swp_to this time so I need to display Sweety's (user id 3) data to John. This means that no matter the logged in user :me i.e., John (user id 1) is in either swp_from or swp_to, I need to display the other user's data to John. That's it. However, it is also displaying John's data to both David and Sweety with the query above. That is what I not want. I am writing query to display the corresponding user's data for John only (i.e., for the logged in user :me only).

Relaxing Music
  • 452
  • 4
  • 13
  • Skip the case expression attempt, simply use AND/OR instead. – jarlh Sep 15 '21 at 18:35
  • I have to join two tables here on different fields based on conditions as you can see. The CASE expression was suggested by user forpas on my question here https://stackoverflow.com/questions/69197092/mysql-join-two-tables-based-on-a-condition/69197435#69197435 – Relaxing Music Sep 15 '21 at 18:37
  • This looks like it will return only the "other" user. If :me is swp_to, you match on swp_from, and vice versa. – Garr Godfrey Sep 15 '21 at 18:38
  • @RelaxingMusic please provide expected results. – Rahul Biswas Sep 15 '21 at 18:41
  • Updated the questions with tables for clear reference. – Relaxing Music Sep 15 '21 at 18:42
  • @RahulBiswas expected results are already explained. – Relaxing Music Sep 15 '21 at 18:42
  • You are doing a LEFT join. This means that you get all the rows of swipes even if there are no matches. Is this what you want? If not, use an INNER join. For your sample data, if `:me=1` you will get 2 same rows: `David 2 like` and for `:me=2` you get 2 same rows `null 2 like`. Isn't this that you want? Here is the fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5529a37cd365603e5edc0579b59e9ad0 – forpas Sep 15 '21 at 18:45
  • In your example, if `:me=1` you only get results for user `2`. I think you need to both change to an `INNER` join and reverse your swp_to/swp_from in your case statement – Garr Godfrey Sep 15 '21 at 18:47
  • @forpas oh yess.... how stupid for me. Didn't pay attention to that. Thanks man! – Relaxing Music Sep 15 '21 at 18:51
  • @GarrGodfrey no I only need to change `LEFT` to 'INNER'. The other one is fine as I also get result for user `3` from the second row. – Relaxing Music Sep 15 '21 at 18:52
  • yes, I see you updated the question to explain you wanted the other user information, not the logged in user. – Garr Godfrey Sep 15 '21 at 18:54
  • @GarrGodfrey Yes, that's right! – Relaxing Music Sep 15 '21 at 18:58
  • Thanks everyone for giving your time and effort. I really appreciate it. Going to sleep well now :) – Relaxing Music Sep 15 '21 at 18:58

0 Answers0