query to imlement block list....

Question

Hai all,

I have two tables a and b as follows to implement a simple block list where users can block other users.....

Table A

+------------+--------------+------+

| Name | phone |userid|

+------------+--------------+------+

| Mr Sasi | 01225 708225 | 1 |

| Miss Brown | 01225 899360 | 2 |

| Mr Black | 01380 724040 | 3 |

+------------+--------------+------+

Table B

+------------+--------------+

| blockedbyid| blockedid |

+------------+--------------+

| 1 | 2 |

| 2 | 3 |

| 1 | 3 |

+------------+--------------+

"blockedbyid" is id of user who has blocked the user in "blockedid".

I need to join the two tables and fetch all records from table A such that the result has all users who are not blocked by a particular user [ie blockedbyid='XXX'].. Can you guys give the SQL query so that i can fetch the records as a recordset??? I dont want to fetch two different rowsets and compare it in php....

Answer 1

Something like this should work

Parameter :USERID

SELECT * FROM TABLEA WHERE userid NOT IN (SELECT blockedid FROM TABLEB WHERE blockedbyid = :USERID)

Answer 2

Using join

SELECT u.* FROM TABLEB b, TABLEA u WHERE b.blockedbyid = 'XXX' AND b.blockedid = NULL

It may work like that, give it a try.

Roadie57 solutions seems better though.