Select from table if id doesn't exist on another table

[arcdigital]

I've got 2 tables

content (id-int, uid-int, catid-int, contenttext-text)

usedblocks (id-int, userid-int, contentid-int)

 

I want to select all the blocks where category id = X that don't exist on usedblocks where userid=X

Here is what I got so far...

select contenttext from content left join usedblocks on content.id=usedblocks.contentid where usedblocks.userid CAN'T FIGURE THIS OUT AND content.categoryid = 1 order by rand();

 

I want to select where usedblocks.userid != 1, but that doesn't work...

 

Can anyone help me? Thanks!

[Maq]

It's not !=

[arcdigital]

I've tried both, Here is the exact query that failed.

 

I'm doing something wrong, because when I do

select contenttext from content left join usedblocks on content.id=usedblocks.contentid where usedblocks.userid <> 1 AND content.categoryid = 1 order by rand() limit 1

 

I don't get what I want (I get nothing).

If I have

category table --  (id=1, name=category1)

content table -- (id=1, categoryid=1, text=AAA)  AND  (id=2, categoryid=1, text=BBB) AND (id=3, categoryid=1, text=CCC)

usedblocks table -- (id=1, userid=1, contentid=2)

 

If I run the query on category 1 for userid 1, I should get the content AAA and CCC (BBB isn't included, since it's in the usedblocks table for user 1)

[Maq]

You may have errors in your query, let's check that first. 

 

Add this to the end of your query and tell me if any errors are displayed:

 

or die(mysql_error());

[arcdigital]

I'm doing it from PHPMyAdmin and it says MySQL returned an empty result set (i.e. zero rows). (Query took 0.0007 sec)

[Maq]

You have:

 

where usedblocks.userid  1

 

And in your sample data you have:

 

usedblocks table -- (id=1, userid=1, contentid=2)

 

which won't return anything cause you don't have a userid from this table where it's not 1...

 

[arcdigital]

Then i'm doing the query wrong. I want to get a list of all the content not listed in the usedblocks section for a specific user.

[kickstart]

Hi

 

Sounds like rather than a join you want a subselect:-

 

SELECT id, contenttext FROM content WHERE categoryid = X AND id NOT IN (SELECT contentid FROM usedblocks) ORDER BY RAND() LIMIT 1

 

All the best

 

Keith

[arcdigital]

Thanks, but I need to take the userid into account. If user 1 uses content id 1 and 2, he should only get 3...but if user 2 uses content id 1, he should be able to get 2 and 3.