Welcome to WebmasterWorld Guest from 54.227.83.19

Forum Moderators: open

Message Too Old, No Replies

JOIN tables with AND AND OR AND state

Joined table is a relational member-block table.

     

JAB Creations

1:58 pm on Mar 12, 2010 (gmt 0)

WebmasterWorld Senior Member jab_creations is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

3:50 pm on Mar 12, 2010 (gmt 0)

10+ Year Member



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

5:04 pm on Mar 12, 2010 (gmt 0)

10+ Year Member



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

JAB Creations

7:43 am on Mar 13, 2010 (gmt 0)

WebmasterWorld Senior Member jab_creations is a WebmasterWorld Top Contributor of All Time 10+ Year Member



syber, AWESOME!

Thank you very much, that worked beautifully!

- John
 

Featured Threads

Hot Threads This Week

Hot Threads This Month