CODEWITHSUNDEEP
mysqlsqlcount
Nate L
Nate L

Reputation: 475

MYSQL using count(1) in the where clause?

I'm doing facial recognition. I have a database of people from group A and people from group B. I want to check every person in A with every person in B. I have a number of different algorithms I'm running to verify the faces. To do this I set up the following tables

comparison (
    id int,
    personA_id int,
    personB_id int,
)

facerecScore (
    id int,
    score int,
    comparison_id int,
    algo_id int,
 )

So lets say I had an eigenfaces program running as my first algorithm I'm testing. Eigenfaces would have an algo_id of 1.

What I want to do is make a query that selects personA and personB from comparison where there exist no existing records in the facerecScore table where algo_id is 1 and the comparison is that comparison.

In other words, if I have already run eigenfaces on these two people, I don't want to run it again. Thus I don't want to select a comparison that already has a record in the facerecscore table with an algo_id of 1

Upvotes: 0

Views: 97

Answers (4)

Aprillion
Aprillion

Reputation: 22324

For anyone who hates correlated subqueries (e.g. for performance reasons, if the original query wasn't optimised), it's possible with a left join and excluding any rows that were actually joined:

Update: Inspired by @penfold's "find all" answer, this is a join+union alternative if the list of algo_ids is known (and short):

select '1' algo_id, c.*
  from comparison c
  left join facerecScore f
    on c.id = f.comparison_id
    and f.algo_id = 1
  where f.id is null
union all
select '2' algo_id, c.*
  from comparison c
  left join facerecScore f
    on c.id = f.comparison_id
    and f.algo_id = 2
  where f.id is null
...

Or a more general one (not sure which one will perform better):

select a.algo_id, c.id
  from comparison c
  cross join (select algo_id from facerecScore group by algo_id) a
  left join facerecScore f
    on c.id = f.comparison_id
    and f.algo_id = a.algo_id
  where f.id is null

Upvotes: 1

mickfold
mickfold

Reputation: 2003

You could try something like the following which will find all rows in comparison which do not have a record in facerecScore for a given algo_id given by the parameter :current_algo 

SELECT *
FROM comparison
WHERE id not in (
    SELECT comparison_id
    FROM facerecScore
    WHERE algo_id = :current_algo
);

In the scenario that you want to find all comparison rows for all algo_ids that do not have a corresponding record in facerecScore then you could use something like the following.

SELECT *
FROM comparison, (SELECT algo_id FROM facerecScore GROUP BY algo_id) algo    
WHERE id not in (
    SELECT comparison_id
    FROM facerecScore
    WHERE algo_id = algo.algo_id
);

Simply this query first finds all combinations of comparison rows and algo_id then removes any which have a record in facerecScore from the result set.

Upvotes: 1

Jimi Kimble
Jimi Kimble

Reputation: 504

SELECT personA_id, personB_id FROM comparison WHERE id NOT IN (SELECT comparison_id FROM facerecScore WHERE algo_id = 1);

This is probably pretty bad on efficiency with the subquery, but it should give you the right results. Possibly someone else can find a more efficient solution.

Upvotes: 0

Abhinav
Abhinav

Reputation: 2085

You can use this, it will return first combination that hasn't been touched. Remove the last part Limit 1,1 and you will get all the combinations that haven't been touched.

SELECT *
  FROM comparison
 WHERE id
not in (
       select comparison_id
       from facerecScore
       where algo_id = 1)
 Limit 1,1

Upvotes: 0

Related Questions