CODEWITHSUNDEEP
mysql
Petro Popelyshko
Petro Popelyshko

Reputation: 1379

Select from table which not in another table with condition

got two tables node:

nid INT(10)
type (VARCHAR(32))

node_hit_count

nid INT(10)
totalcount (bigint(20))

i need to select all nids from node table which are not in node_hit_count table and WHERE node type is equal to feature or home or competition

What i have tried, its wrong, and my mind is gonna blow :/

select * from node left join node_hit_counter on node.nid=node_hit_counter.nid 

where hit.nid is null and node.type IN ('feature', 'home', 'competition')

Upvotes: 0

Views: 99

Answers (2)

Sir Rufo
Sir Rufo

Reputation: 19096

try this and you can use your hit.nid in where clause :o)

select n.* 
from node n
left join node_hit_counter hit on n.nid=hit.nid 
where hit.nid is null 
and n.type IN ('feature', 'home', 'competition')

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

What is the hit.nid in the WHERE clause. You have to use WHERE node.nid IS NULL instead like so:

select node.*
from node 
left join node_hit_counter on node.nid = node_hit_counter.nid 
where node.nid is null 
  and node.type IN ('feature', 'home', 'competition');

OR:

SELECT *
FROM node
WHERE nid NOT IN(SELECT nid FROM node_hit_counter WHERE nid IS NOT NULL)
  AND type IN ('feature', 'home', 'competition');

Upvotes: 2

Related Questions