[SOLVED] Query Question

[jboog]

gotta be something small...

 

quivergroup is made up of boards.

Fetch the subscription to 'subscribed''s boards if privacy is 1 and 'user' isn't on 'subscribed''s block list.

Something around the NOT?

 

SELECT boards.boardid, boards.userid, boards.boardname, brand, model, lengthft, lengthin, quivergroup.quid, quivergroup.quivername, boards.timestamp
FROM boards,subscriptions,quivergroup,messageblocks
WHERE subscriptions.username = 'user'
AND subscriptions.resource = 'boards'
AND (quivergroup.privacy = '1' AND NOT (messageblocks.username = subscriptions.subscribed AND messageblocks.blockeduser = 'user'))
AND boards.userid = subscriptions.subscribed
AND boards.quid = quivergroup.quid
ORDER BY boards.timestamp DESC LIMIT 1

 

Subscriptions:

id,username,subscribed,resource,timestamp

1, 'user', 'user2', 'boards', '200704221523'

 

Quivergroup:

id,userid,quivername,privacy,picgal,timestamp

9, 'user2', 'Old Boards', 1, 12, '200703181551'

 

Boards:

id,userid,boardtitle,etc...

34, 'user2', 'asd', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, '', 'Bat', 'FCS', 1, '', '', 9, '200704221648'

 

Messageblocks:

empty

 

Thanks

 

[jboog]

Also if I take out

 

AND NOT (messageblocks.username = subscriptions.subscribed AND messageblocks.blockeduser = 'user')

 

it works

[bubblegum.anarchy]

Rewrite the entire query using join syntax to join tables rather than the where clause - makes reading queries a great deal easier (for me anyway) and you may come across the error in the process.

[jboog]

SELECT boards.boardid, boards.userid, boards.boardname, brand, model, lengthft, lengthin, quivergroup.quid, quivergroup.quivername, boards.timestamp
FROM boards
LEFT JOIN quivergroup
ON boards.quid = quivergroup.quid
LEFT JOIN subscriptions
ON boards.userid = subscriptions.subscribed
LEFT JOIN messageblocks
ON boards.userid = messageblocks.username
WHERE subscriptions.username = 'user'
AND subscriptions.resource = 'boards'
AND (quivergroup.privacy = '1' AND messageblocks.blockeduser != 'user')
ORDER BY boards.timestamp DESC LIMIT 1

 

So my new question is: I want to get results where there is no row in messageblocks where messageblocks.username = boards.userid AND messageblocks.blockeduser = 'user'...The above query doesn't work if messageblocks.username has no rows in messageblocks. Any ideas?

 

Thanks

[bubblegum.anarchy]

If I understand you correctly you need to add the following to the WHERE clause:

 

AND messageblocks.username IS NULL

[jboog]

that did it. Thank you.