Msg#: 4096320 posted 1:58 pm on Mar 12, 2010 (gmt 0)
I have two MySQL tables; one for members (with their row id) and a relational table with two id's for 'blocked' and 'blocker'. I'm trying to generate either two or more rows or to have a non-null value appear in the result to determine if the person's profile you are viewing is disallowed because of a block (either having blocked them or being blocked by them).
So let's say your user id is '1' and their user id is '2'. In the block relational table I'm looking for...
blocked='1' AND blocker='2' OR blocked='2' AND blocker='1'
If either situation produces a result that means someone blocked someone else (who blocked who does not really matter as the result implies the profile appears private).
So here is the regular MySQL query before the JOIN...
SELECT ma.member_id, ma.member_name FROM member_accounts AS ma WHERE ma.member_name='jabcreations'
...and adding the JOINS (this and numerous iterations like this query do not work as desired)...
LEFT JOIN member_block AS mb1 ON (ma.member_id=ub1.id_blocked)
LEFT JOIN member_block AS mb2 ON (ma.member_id=ub2.id_blocker)
AND mb1.id_blocked=ma.member_id AND mb1.id_blocker=1
OR mb2.id_blocked=1 AND mb2.id_blocker=ma.member_id
Again I don't want the query to fail if no block is found and I don't particularly care how I determine if either user has blocked the other user be it a row that exists (e.g. isset($row1['id_blocked']) or MySQL returns more then one row) my main goal here is to achieve this in a single query.
Msg#: 4096320 posted 3:50 pm on Mar 12, 2010 (gmt 0)
You can do this without using JOIN:
SELECT ma.member_id, ma.member_name, (SELECT COUNT(*) FROM member_block WHERE id_blocked = ma.member_id) AS blocked, (SELECT COUNT(*) FROM member_block WHERE id_blocker = ma.member_id) AS blocking, FROM member_accounts AS ma WHERE ma.member_name='jabcreations'