Solving mysql query

Question

Part 1:

Software: Mysql workbench

Objective: To find the 2nd match for a particular user

Table: Profile_match

Columns: a_profile_id, b_profile_id, a_profile_match_available_on, b_profile_match_available_on

My process:

1. Find all the matches for a particular user

2. Find all the dates of the match for that user

3. Find the 2nd match (id, date) for that user

My Queries:

select * from profile_match
where (a_profile_id = '*****' and a_profile_match_available_on is not null)
or (b_profile_id = '*****' and b_profile_match_available_on is not null)
order by a_profile_match_available_on asc limit 1,1;

Complication:

1. A particular user can be either under a_profile_id or b_profile_id, so the date he is match can be a_profile_match_available_on or b_profile_match_available_on

2. The 'order by' doesn't help as the required date can be either on a_profile_match_available_on or b_profile_match_available_on

As seen in the photo (after export to excel), the highlighted profile id is the user I am looking at, and the highlighted dates are the day the user receive the match.

Desired outcome:

Outcome 1: Maybe can create a new column containing all the id of people matched with that user (non-highlighted id) and another column containing the all the highlight dates

Outcome 2: Maybe just have 4 columns. 1 column is id of user, 1 column is id of the user match, 1 column is date user is match, 1 column is date where user's match is match

~~~~Illustrated by User Dharmesh Patel~~~~: fiddle

The 2nd problem is that this query is only for a particular user. How do I expand this such that I can find the 2nd match for all my users?

Part 2:

Objective: To find the 2nd mutual like for all users

Current progress: I can find the 2nd mutual like for a particular user, but my query can't do it for all my user

Query:

select * from profile_match
where (a_profile_id = '*****'
or b_profile_id = '*****')
and (a_profile_match_status = 1 and b_profile_match_status = 1)
order by created_on asc limit 1,1;

Info: a_profile_match_status and b_profile_match_status only takes value of 0 or 1. When both is 1, it means both users like each other. Created_on is simply the date both users indicate like.

Thanks guys!

Answer 1

For Part 1 you can try following query:

SELECT * FROM profile_match
WHERE (a_profile_id = 1 AND a_profile_match_available_on is not null)
    OR (b_profile_id = 1 AND b_profile_match_available_on is not null)
ORDER BY (case when a_profile_id=1 
    THEN a_profile_match_available_on 
    ELSE b_profile_match_available_on end
) asc limit 1,1;

check following fiddle

UPDATE

check following modified queries:

PART 1

SELECT * FROM (SELECT P1.* FROM 
    (SELECT 
        a_profile_id AS profile_id, 
        a_profile_match_available_on as profile_match_available_on 
    FROM profile_match 
    UNION SELECT 
        b_profile_id  AS profile_id, 
        b_profile_match_available_on as profile_match_available_on 
    FROM profile_match) AS P1
) AS P2
GROUP BY P2.profile_id
HAVING P2.profile_id=1 AND 
    profile_match_available_on > MIN(profile_match_available_on);

PART 2

SELECT * FROM (SELECT P1.* FROM 
    (SELECT 
        a_profile_id AS profile_id, 
        a_profile_match_available_on as profile_match_available_on 
    FROM profile_match 
    UNION SELECT 
        b_profile_id  AS profile_id, 
        b_profile_match_available_on as profile_match_available_on 
    FROM profile_match) AS P1
) AS P2
GROUP BY P2.profile_id
HAVING profile_match_available_on > MIN(profile_match_available_on);