Click here to Skip to main content
13,703,968 members
Rate this:
 
Please Sign up or sign in to vote.
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
Updated 19-Jun-18 2:08am
v4
Rate this: bad
 
good
Please Sign up or sign in to vote.

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:
SELECT MIN(ID) FROM ItemMasterlistTable GROUP BY [Generic Name] HAVING [Generic Name] IS NOT NULL

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:
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
  Permalink  
Comments
JKwaaan 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!
0x01AA 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?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

select [Generic Name] from Table where isnull([Generic Name],'')<>'' group by [Generic Name]
  Permalink  
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"

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180920.1 | Last Updated 19 Jun 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100