I'm sorry if I'm repeating that question, but, I haven't found anything that could help me.
Anyway, that's the question:
I'm writing an dating aplication, in this app, I'm bugging in the part of mutual interest.
I mean, you'll have 3 options about a person (do you wanna date with him/her? Yes, maybe, no).
So, I've create a table, that saves your userId, the persons'id that you wanna date, and your response (yes, no or maybe).
Is there a way to compare if you said yes or maybe, and the other person says yes or maybe, will return something like a mutual person?
something like (if you said yes or no, and the person said yes or no then select all mutual persons).
I'm sorry if sounds confuse, but in case of doubt, I can explain better.
My English doesn't help, though.... I'm Brazilian
Something like this,
SELECT LEAST(a.UserID, a.PersonID) AS Person1,
GREATEST(a.UserID, a.PersonID) AS Person2,
a.Response
FROM TableName a
INNER JOIN TableName b
ON a.UserID = b.PersonID AND
a.PersonID = b.UserID AND
a.Response = b.Response
GROUP BY Person1, Person2
Assuming you have records like this,
╔════════╦══════════╦══════════╗
║ USERID ║ PERSONID ║ RESPONSE ║
╠════════╬══════════╬══════════╣
║ 1 ║ 2 ║ yes ║
║ 2 ║ 1 ║ yes ║
║ 3 ║ 4 ║ no ║
║ 4 ║ 3 ║ yes ║
║ 5 ║ 6 ║ maybe ║
║ 6 ║ 5 ║ maybe ║
╚════════╩══════════╩══════════╝
The OUTPUT of the query is
╔═════════╦═════════╦══════════╗
║ PERSON1 ║ PERSON2 ║ RESPONSE ║
╠═════════╬═════════╬══════════╣
║ 1 ║ 2 ║ yes ║
║ 5 ║ 6 ║ maybe ║
╚═════════╩═════════╩══════════╝
SELECT him.id as "him",
her.id AS "her",
FROM match him, match her
WHERE him.id = her.id OR her.id=him.id;
You can extent out as required... to match on the yes / no / maybe field.
Will this work?
SELECT * FROM test t1 INNER JOIN test t2 ON t1.id = t2.personid AND t2.id=t1.personid WHERE t1.response=t2.response;
http://www.sqlfiddle.com/#!2/9170c/1
If you want the results for a specific response (Yes, May be), you can add that in the WHERE conditions.
Something like:
Select Userid As p1, Personid p2
From YourTable
Where (Response like "Yes" OR Response like "Maybe") AND p2 IN(
Select Userid
From YourTable
Where Userid = p2 AND Personid = p1 AND (Response like "Yes" OR Response like "Maybe");
thank you so much. According to your answers, I've reached this:
SELECT p2.atirador AS mutual
FROM paqueras AS p1, paqueras AS p2
WHERE p2.alvo = $UserID
AND p1.atirador = $UserID
AND p2.atirador = p1.alvo
AND (
p1.resposta1 = "sim"
OR p1.resposta1 = "talvez"
)
AND (
p2.resposta1 = "sim"
OR p2.resposta1 = "talvez"
)
What I've did? I'll try explain, I'm getting all the person that is mutual with the user ID. That'll come handy when the user access his mutual page, you know?
Well, so far so good... thank you guys, I'll report any bug. But let's hope not ;-)