CODEWITHSUNDEEP
mysqlsqlcategories
user862010
user862010

Reputation:

Do not bring a line of tables

Alright, let's to business ...

say I have a category (categoyid) '150 ', and would not bring anything that is in that category .....

It turns out that an article may be in multiple categories, and the more I block 150 in the category SELECT, it will still come to be linked to other categories ....

How do I, so that any item in the category '150 'is not sought in the SELECT even though he was also in another category than the '150' ...

Tables:

node

nodeid
contentid
url
publishdate

nodeinfo

nodeid
title

node_category

categoryid
nodeid

article

contentid
previewimage

===================== I tried :

SELECT p.nodeid, p.contentid p.publishdate, p.url, c.categoryid, c.nodeid, a.previewimage, a.contentid, e.title FROM `node` AS p 
INNER JOIN `nodecategory` AS c ON p.`nodeid` = c.`nodeid` 
INNER JOIN `article` AS a ON p.`contentid` = a.`contentid` 
INNER JOIN `nodeinfo` AS e ON p.`nodeid` = e.`nodeid` 
WHERE c.`categoryid` 
IN (73,74,77,105,71,70,72,76,100,80,79,78,81,108,145,146,82,142,83,97,153) 
GROUP BY c.nodeid 
ORDER BY p.`publishdate` 
DESC LIMIT 4

Upvotes: 0

Views: 50

Answers (1)

Mike Partridge
Mike Partridge

Reputation: 5281

I think you need a not-exists clause:

 AND NOT EXISTS (
  SELECT 1
  FROM 
   `nodecategory` AS ic
  WHERE
   p.`nodeid` = ic.`nodeid`
   AND ic.`categoryid` IN (150)
 )

Here it is in your query, reformatted a bit:

SELECT 
 p.nodeid, 
 p.contentid,
 p.publishdate, 
 p.url, 
 c.categoryid, 
 c.nodeid, 
 a.previewimage, 
 a.contentid, 
 e.title 
FROM 
 `node` AS p 
 INNER JOIN `nodecategory` AS c ON p.`nodeid` = c.`nodeid` 
 INNER JOIN `article` AS a ON p.`contentid` = a.`contentid` 
 INNER JOIN `nodeinfo` AS e ON p.`nodeid` = e.`nodeid` 
WHERE c.`categoryid` 
 IN (73,74,77,105,71,70,72,76,100,80,79,78,81,108,145,146,82,142,83,97,153) 
 AND NOT EXISTS (
  SELECT 1
  FROM 
   `nodecategory` AS ic
  WHERE
   p.`nodeid` = ic.`nodeid`
   AND ic.`categoryid` IN (150)
 )
GROUP BY c.nodeid 
ORDER BY p.`publishdate` 
DESC LIMIT 4

This should filter out nodes that are in one of the chosen categories, but are not also in those specified in the not-exists clause.

Upvotes: 1

Related Questions