SQL QUERY选择禁止列表，子和父连接

Well I've build the following tables (only the involving column)

table forums:

id | forumname    | relatedto
------------------------------
1  | games        | 0
2  | action games | 1      // subforum of forum games
3  | rpg games    | 1      // subforum of forum games

table banlist

id | useraid | forumid
----------------------
1  | 56      | 1           // the user 56 got banned from forum games

What I'm trying to do is build a function to see if a user got banned from forum X, if he got banned he consider as banned from the forums who's related to forum X. for ex: user 56 is banned also from forum rpg games.

I've write the following query which didn't worked:

    SELECT 
    f.id,
    f.relatedto,
    b.useraid as u1,
    b2.useraid as u2
    FROM forums f
    LEFT JOIN banlist b ON(b.forumid=f.id and b.useraid='56')
    LEFT JOIN banlist b2 ON(b2.forumid=f.relatedto and b2.useraid='56')
    WHERE f.id='2'

Any ideas?

So something like...

SELECT f.* FROM forums f JOIN banlist b ON b.forumid = f.id OR b.forumid = f.parent_id;

...?

I think your query was pretty close assuming you want to return results if the user isn't banned and don't want to return results if the user is banned:

SELECT *
FROM forums f
  LEFT JOIN banlist b ON f.id = b.forumid AND b.useraid = 56
  LEFT JOIN banlist b2 ON f.relatedto = b2.forumid AND b2.useraid = 56
WHERE b.id IS NULL 
  AND b2.id IS NULL
  AND f.id = 2

SQL Fiddle Demo

You may need to use OR for your IS NULL check -- depends on your desired results (if child is banned but parent isn't for example).

I've found the answer.

SELECT 
  f.id,
  count(b.id) AS isbanned
FROM forums f 
LEFT JOIN banlist b
  ON (
    (
      b.forumid = f.id OR b.forumid = f.relatedto
    )
    AND userid = '$userid'
  )
WHERE f.id = '$forumid'

To find out if the user got banned or not, you can create a function or a proc passing it a useraid.

Create proc userstatus @useraid int As IF (SELECT COUNT(*) FROM BANLIST WHERE USERAID = @USERAID) > 0 BEGIN PRINT” USER IS BANNED’ ELSE PRINT “USER IS NOT BANNED” END