CODEWITHSUNDEEP
mysqlsql
anon
anon

Reputation:

Select records without any related records of some types

Basically I have two table

articleID 
  1
  2
  3
  4


relatedType | articleID
     3              1
     4              1
     3              2
     4              3
     5              3
     2              4

I need to select the articleID that doesn't have any related records with type > 3. With this dataset I basically need:

articleID
2
4

Because their related type contain only 3 and 2.

I con do it with this query:

SELECT * FROM article 
   WHERE articleID NOT IN (SELECT articleID FROM relatedTable 
                     ^         WHERE type > 3 GROUP BY portalid ) 
                     |
                     |--- NOT IN does the trick!

BUT I would like to avoid nested query because this query is pretty slow. Any hint?

Upvotes: 0

Views: 91

Answers (3)

Zoltan Fedor
Zoltan Fedor

Reputation: 2097

Use OUTER JOIN 

SELECT a.articleID 
FROM article a LEFT OUTER JOIN relatedTable r
 ON (a.articleID = r.articleID and r.relatedType > 3)
WHERE r.articleID IS NULL

CORRECTION: Sorry, I just realized that the request was not to have those rows listed which has ANY records with type > 3. You can still do it by having a sub-query in the JOIN or by creating a temp table, indexing it and then joining that. Whether any of these are actually faster than the NOT IN sub-query will depend on MySQL version and more importantly table size and stats.

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

If you need only article id, try this:

SELECT 
   articleID
FROM relatedTable
GROUP BY articleID
HAVING MAX(relatedType) <= 3

or you can JOIN this to your article table.

Upvotes: 0

a1ex07
a1ex07

Reputation: 37354

You can do

SELECT * FROM article a
 WHERE NOT EXISTS 
 (SELECT NULL FROM relatedTable b WHERE b.type > 3
  AND b.articleID = a.articleID)

Technically, all 3 ways to achieve the desired results (NOT IN, NOT EXISTS, LEFT JOIN) should behave the same (for non-nullable column) and normally generate the same execution plan except mysql where NOT IN is not recommended (or wasn't recommended prior to 5.5, maybe it changed).
I'd also blame GROUP BY in your original query for poor performance as well...

Upvotes: 1

Related Questions