ket ket - 5 months ago 7
MySQL Question

How to left join with or operator and determined only one column that match

I've search the internet for days and found no solution to my problem.
I've two simple tables here:

TB1
tb1_id tb1_name
1 Red
2 Blue
3 Yellow

TB2
tb2_id tb2_id1 tb2_id2 tb2_type tb2_value
1 3 1 A 2


Suppose table 1 is list of users. The guy called Red want to have relationship with a guy called Yellow. So he send a request. When Yellow accepted the request I update tb2_value to 2.

Several days later, Yellow logged in and see who he has relationship with.
How to do a query for this where both guys can see each other too.
Here's my attempt so far and it's not working:

SELECT
tb2_id1, tb2_id2, tb2_type, tb2_value, tb1_id, tb1_name
FROM
TB2
LEFT JOIN
TB1
ON
tb2_id1 = tb1_id OR tb2_id2 = tb1_id
WHERE
(tb2_id1 = :user_id AND tb2_id2 != :user_id) OR (tb2_id1 != :user_id AND tb2_id2 = :user_id) AND tb2_type = :tb2_type AND tb2_value = :tb2_value AND RAND()<(SELECT ((15/COUNT(*))*10) FROM TB2)
ORDER BY
RAND()
LIMIT
15


Or do I do it wrong?

Thank you,

Answer

Try this:

SELECT 
    TB2.tb2_id1, TB2.tb2_id2, TB2.tb2_type, TB2.tb2_value,
    IF(TB2.tb2_id1 = :user_id, t2.tb1_id, t1.tb1_id) AS tb1_id, -- :user_id = 1
    IF(TB2.tb2_id1 = :user_id, t2.tb1_name, t1.tb1_name) AS tb1_name -- :user_id = 1
FROM TB2
LEFT JOIN TB1 t1
ON TB2.tb2_id1 = t1.tb1_id
LEFT JOIN TB1 t2
ON TB2.tb2_id2 = t2.tb1_id
WHERE (TB2.tb2_id1 = :user_id OR TB2.tb2_id2 = :user_id) -- :user_id = 1
AND TB2.tb2_type = :tb2_type -- :tb2_type = 'A'
AND TB2.tb2_value = :tb2_value -- :tb2_value = '2'
-- RAND()<(SELECT ((15/COUNT(*))*10) FROM TB2)
ORDER BY RAND()
LIMIT 15

SQLFiddle Demo