CODEWITHSUNDEEP
mysqlsql
Artemkller545
Artemkller545

Reputation: 999

SQL select count of items that not in other table with additional column check

The title doesn't make sense because it's hard to explain what I want in the title, you can read this because it was explained and answered here

So I have the following query:

SELECT COUNT(*) AS missing
FROM sites
WHERE id NOT IN (
             SELECT DISTINCT site_id
             FROM callback_votes
             WHERE ip='84.108.27.143')

This returns the amount of sites that haven't been voted on, returns 0 if voted on all. (see the link, it's explained)

But in the database, I have site type that has a column named module, so what I want to do is, check if sites.module equals to 'none', then count it as NOT IN, like it does in that query.

So let's say you have 2 sites in the db, you never voted for any - this query will return 2 but with the 'none' check, it will only return 1 because one of the sites have module set to 'none'. Very similar to what I asked few minutes ago, but it's pretty complex in this case

I tried doing things like this:

SELECT COUNT(*) AS missing
FROM sites
WHERE id NOT IN (
             SELECT DISTINCT site_id
             FROM callback_votes
             WHERE ip='84.108.27.143')
         OR module = 'none'

But no luck.

Is there a way to do this in this query?

Upvotes: 0

Views: 2219

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Formally, you could change your not in to a not exists and include the logic there. I think this is the logic:

SELECT COUNT(*) AS missing
FROM sites s
WHERE NOT EXISTS (SELECT 1
                  FROM callback_votes cv
                  WHERE ip = '84.108.27.143' AND cv.site_id = s.id and s.module <> 'none'
                 );

But then again, I think this is equivalent to your original logic (unless some of the fields can take on NULL values. Perhaps this gets you want you want:

SELECT COUNT(*) AS missing
FROM sites
WHERE id NOT IN (
             SELECT DISTINCT site_id
             FROM callback_votes
             WHERE ip='84.108.27.143') AND
      module <> 'none';

Upvotes: 2

Related Questions