How do I retrieve both non empty and non duplicate data in SQL?

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I have data that duplicates and a column that is sometimes null (depending on what product it is).

Note: All the data is from 1 table.

Example:

ID         |Generic Name|Description
0001        Cetirizine   Allerzet 10mg
0002        Cetirizine   Alnix 10mg
0003                     Disposable tube
0004        Paracetamol  Biogesic Tablet

My plan is to retrieve the Generic Name into a combo box without the duplicates.

What I have tried:

So far I have this code to eliminate the null data. I'm having trouble trying to retrieve only 1 generic name from the duplicates.

Code:

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

Posted 15-Jun-18 19:40pm

User 12225285

Updated 19-Jun-18 2:08am

v4

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-06-15T20:14:00

Solution 1

The problem is that when you have multiples, you have to decide for SQL which row to return - it doesn't know if you want "Cetirizine" to be "Allerzet 10mg" or "Alnix 10mg" and it hates to throw away information.
You can select the first or last:

SQL

SELECT MIN(ID) FROM ItemMasterlistTable GROUP BY [Generic Name] HAVING [Generic Name] IS NOT NULL

SQL

SELECT MAX(ID) FROM ItemMasterlistTable GROUP BY [Generic Name] HAVING [Generic Name] IS NOT NULL

And then use JOIN to retrieve the info you want:

SQL

SELECT g.ID, i.[Generic Name], i.Description 
FROM (SELECT MIN(ID) as ID 
      FROM ItemMasterlistTable 
      GROUP BY [Generic Name] 
      HAVING [Generic Name] IS NOT NULL) g
JOIN ItemMasterlistTable i 
     ON g.ID = i.ID

Posted 15-Jun-18 20:14pm

OriginalGriff

Comments

[no name] 16-Jun-18 2:19am

I updated my sql query. I don't know if it will impact anything.

Without using JOIN can I still do it? The data above is from 1 table only. Almost every samples I see when I search for help suggests JOIN.

OriginalGriff 16-Jun-18 2:55am

That's because a JOIN is the right way to do it: SQL understands JOINs and can do them very, very efficiently. The alternative is to create and fill a temporary table and use IN, but that's really nasty at the best of times.
Because you need GROUP BY to "collect" the rows into "bunches", and GROUP BY can only work with aggregated data (SQL doesn't like making decisions as to which row to use for you, remember) you need to use a GROUP BY subquery to select the unique IDs of the rows you want to use, then JOIN that data back to the original table to get the actual row data. It's pretty simple to work with once you get your head round!

[no name] 16-Jun-18 10:43am

What I'm missing? Is this not simply to solve with a SELECT DISTINCT [Generic Name] FROM ItemMasterlistTable WHERE NOT [Generic Name] IS NULL?

kirthiga S · Answer 2 · 2018-06-19T02:09:00

Solution 2

select [Generic Name] from Table where isnull([Generic Name],'')<>'' group by [Generic Name]

Posted 19-Jun-18 2:09am

kirthiga S

Comments

CHill60 20-Jun-18 2:21am

All you have done is copy the code from someone else's comment, tweak the null handling having assumed that blank is not required, and then added a totally unnecessary "group by"