I'm trying to figure out how to go about this. I want MySQL to select the results where the ID of the result isn't in this certain other table.

PHP Code:

$query = mysql_query("select * from links as l inner join links_pro as lp on l.id != lp.link where l.expires > NOW() && l.active = 1 && l.category = '".$category['id']."' order by id desc limit 20"); 


That's the only suggestion I have so far, however it doesn't seem to be functioning correct. I'm presuming it's not that simple? Or maybe I've gone OTT? Suggestions!

in that case, an inner join is not your friend. Try with a "not exists" rather.
Note that as I do't use mysql, I'm not sure it's supported.
But in theory, it would be the way to go:

select * 
from links as l 
where not exists (
  --this means, that we don't care about the data, we just want to know if the row exists.
  select 1
  from links_pro as lp
  where lp.id=l.id
)
and l.expires > NOW() 
and l.active = 1 
and l.category = '$category['id']' 
order by id desc 
limit 20

select t1.* from table1 t1 left join table2 t2 on t1.field = t2.field where t2.field is null

This will select all records from table 1 which has no corresponding records in table2, assuming that table1 and table2 are linked through 'field' column.

"t1 join t2 on t1.field1 != t2.field2" will produce a table with M*N rows where M is number of rows in t1 and N is number of rows in t2. This is definitely not what you want.

following tripy's advice keep in mind that subqueries most often work slower than joins, especially on simple queries like yours

Thanks!

Worked great.

Originally Posted by mtishetsky

following tripy's advice keep in mind that subqueries most often work slower than joins, especially on simple queries like yours

Your solution made me wonder if I did overlooked something.
Checking it, I understood that "left join" is a shortcut for "left outer join", which makes perfect sense.

But on the performance statement, I do think they are mostly equals.
outer join are hard on the query planner too.
At least on pgsql and ms sql server, I see very little differences in term of performances between an outer join and a not exists condition.

I didn't used mysql for several years, so I could not say for sure how the planner handled those 2 cases.