Check for Double items and then check if certain colomn is empty in SQLITE database

Question

Want to check if there are double items in Column Naam in Table zeeboot , and then i show them in a combobox. Show the result is not the problem but the Statement is .

This is working

Select Naam FROM zeeboot GROUP BY Naam HAVING COUNT(Naam) > 1

But also i want to filter if the Naam is double check if Bijzonderheden IS NULL or empty

Show double Naam where Bijzonderheden is empty

Tried this but the outcome is not good.

SELECT Naam 
FROM zeeboot 
GROUP BY Naam 
HAVING COUNT(Naam) > 1 
AND SUM(CASE WHEN Bijzonderheden IS NULL OR Bijzonderheden = '' 
THEN 1 ELSE 0 END) > 0

And i think there is another thing , what if there are double Naam and both Bijzonderheden are not empty

Using SQL Server Compact Edition database version 3.5

Sal Young · Accepted Answer · 2024-10-09 17:32:12Z

0

Based on your problem description, I came up with a sample table and data and here is what I recommend. The moment you try to use the two predicates in the "HAVING" section, it will not work. So instead, using a sub-query is the way to go.

By the way, when submitting questions related to queries, it will help us if you provide a good code sample to reproduce the scenario. I used http://sqlfiddle.com/ to write and test the code below.

CREATE TABLE zeeboot (
  Naam INTEGER,
  Bijzonderheden);

INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (1,1);
INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (1,2);
INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (1,NULL);
INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (2,1);
INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (2,2);
INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (2,3);
INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (2,'');
INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (3,1);
INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (4,1);
INSERT INTO zeeboot(Naam, Bijzonderheden) VALUES (5,'');

SELECT O.Naam 
  FROM zeeboot O
 WHERE EXISTS (SELECT I.Naam 
                 FROM zeeboot I
                WHERE IFNULL(I.Bijzonderheden,'') = ''
                  AND I.Naam = O.Naam)
 GROUP BY O.Naam
HAVING COUNT(O.Naam) > 1;

answered Oct 9, 2024 at 17:32

Sal Young

11 bronze badge

Sign up to request clarification or add additional context in comments.

3 Comments

Bas H Over a year ago

Thanks for the reply Sal , i have this error . System.Data.SqlServerCe.SqlCeException: 'There was an error parsing the query. [ Token line number = 3,Token line offset = 3,Token in error = From ]'

Sal Young Over a year ago

Why are you using SQL Server Compact Edition (System.Data.SqlServerCe) library? You should be using System.Data.Sqlite or Microsoft.Data.Sqlite library. Or perhaps, why did you post your question with the tag "sqlite" if you are using SQL Server Compact Edition

Bas H Over a year ago

Oh sorry Sal Young i see it now , my mistake . Edited my Question

Collectives™ on Stack Overflow

Check for Double items and then check if certain colomn is empty in SQLITE database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related