JOIN tables with AND AND OR AND state

Joined table is a relational member-block table.

[JAB Creations]

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)...

SELECT ma.member_id, ma.member_name, mb1.id, mb2.id

FROM member_accounts AS ma

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)

WHERE ma.member_name='jabcreations'

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.

- John

[syber]

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' 

[syber]

just noticed a typo - there shouldn't be a comma after AS blocking

[JAB Creations]

syber, AWESOME!

Thank you very much, that worked beautifully!

- John