homepage Welcome to WebmasterWorld Guest from 54.163.84.199
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
JOIN tables with AND AND OR AND state
Joined table is a relational member-block table.
JAB Creations




msg:4096322
 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)...

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




msg:4096401
 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'

syber




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

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

JAB Creations




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

syber, AWESOME!

Thank you very much, that worked beautifully!

- John

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved