How do I retrieve non-empty & non-duplicate data from the database?

Question

I have this nitpicked columns on my table (cause the rest are irrelevant in the problem).

ID   | Generic Name
-----+---------------
001  | Cetirizine
002  | Cetirizine
003  |
004  | Paracetamol

I want my combo box to display only a single entry Cetirizine (or any data that has been duplicated) and no empty generic names (some data have no generic names).

I've tried:

select 
    Item_GenName 
from 
    ItemMasterlistTable 
where 
    nullif(convert(varchar, Item_GenName), '') is not null

but it only achieves the no empty data part.

I've tried using DISTINCT, but it doesn't work and somebody suggested JOIN but I don't think it works since I'm only using 1 table.

I've also tried:

SELECT 
    MIN(Item_ID) AS Item_ID, Item_GenName
FROM
    ItemMasterlistTable
GROUP BY 
    Item_GenName

but there's always an error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

It would better if you add script (Table and sample data) and expected output — Prashant Pimpale, Jun 16 '18 at 07:57
To get non duplicates in linq you can use a GroupBy and then get the first item from each group. Null you need to change that the value does not equal Db.Null. — jdweng, Jun 16 '18 at 08:09
`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) — marc_s, Jun 16 '18 at 11:27

OneManBand · Accepted Answer · 2018-06-16T08:28:09.490

2

The following query should return only distinct, non-empty Item_GenNames:

SELECT DISTINCT Item_GenName
FROM ItemMasterlistTable
// because Item_GenName is of type *text*, the below in lieu of `is not null` and `!= ''`
WHERE datalength(Item_GenName) != 0

You said you tried DISTINCT and it did not work so I want to clarify,

The DISTINCT keyword will return unique records over the complete domain of your select statement. If you include the ID column in your select statement, even a distinct selection will return your duplicate Item_GenNames b/c the combined ID / Item_GenName record would be unique. Include only Item_GenName in your select clause to guarantee distinct values for this column.

edited Jun 16 '18 at 08:28

answered Jun 16 '18 at 08:20

OneManBand

528
5
24

The 'text' datatype is deprecated so I don't have much experience with it but you may be able to check for null/empty with [this method](https://stackoverflow.com/a/33425/2418006). I will edit my answer to reflect. – OneManBand Jun 16 '18 at 08:26
When I tried this on the sql server, it worked! But when I applied it on my c# code, it keeps having this error saying "Column 'Item_GenName' does not belong to table.". – Jun 18 '18 at 05:31
What I did to fix it was to put "as Item_GenName". – Jun 18 '18 at 07:10

sacse · Answer 2 · 2018-06-16T08:24:12.403

1

The following query might be useful.

    declare @tab table (ID varchar(10), Generic_Name varchar(100))
    insert into @tab
    select '001',   'Cetirizine'
    union 
    select '002',   'Cetirizine'
    union 
    select '003',   ''
    union 
    select '004',   'Paracetamol'

    select MIN(substring(ID, 1, 10)) ID, substring(Generic_Name, 1, 1000) Generic_Name
from @tab
where substring(Generic_Name, 1, 1) <> ''
group by substring(Generic_Name, 1, 1000)

edited Jun 16 '18 at 08:24

answered Jun 16 '18 at 07:54

sacse

3,634
2
15
24

I forgot to put that my ID and Generic Name is formatted as text. Tried the query below as a test run on the sql server and there's this error: "The data types text and varchar are incompatible in the equal to operator." – Jun 16 '18 at 08:11
It worked in the sql server but keeps having this error: "Column 'Item_GenName' does not belong to table" when I put it in my c# code. – Jun 18 '18 at 05:32

score 1 · Answer 3 · answered Jun 16 '18 at 16:42

1

You can try this query

Select  distinct Item_GenName FROM(
Select * FROM ItemMasterlistTable where Item_GenName <> ''
)t

Inner query remove non-empty records and outer query get the distinct record from the inner output

answered Jun 16 '18 at 16:42

Krunal Soni

153
6

How do I retrieve non-empty & non-duplicate data from the database?

3 Answers3