Click here to Skip to main content
15,346,335 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a set of empid like
kti001
kti001
kti002
etc.
in an array.

Now using "in" clause like
select empid from table where empid in ('kti001','kti001','kti002')
returns distinct data
kti001
kti002

using "or" clause also returning same results.

But I want to return data according to empid supplied even it is duplicate.

How to do it?

Note: empid list is dynamic.I'm making the where clause part by looping through the empid list.So don't give an answer which is for static query or for which making dynamic is complex.
Posted
Updated 23-Sep-21 17:00pm

1 solution

Based on the description the two rows containing
VB
kti001
kti001

are not duplicates. Otherwise the SELECT statement would return both of those.

In SQL if you want to eliminate duplicate rows you need to specify DISTINCT in your query. Since you haven't used that keyword, duplicates are allowed.

Having that said the only option is that there is a difference between the rows, perhaps a trailing space or something.

To find out the differences have a try with something like this on your dynamic data
SQL
select '>' + empid  + '<' from table

That should help you to visualize possible spaces within the data.

[Edit]
As an example that using an IN operator does not remove duplicates, consider the following statements:
Create the test table
SQL
CREATE TABLE TestTable1 (
   EmpId   VARCHAR(MAX)
);

Add initial rows
SQL
INSERT INTO TestTable1 (EmpId) VALUES ('kti001');
INSERT INTO TestTable1 (EmpId) VALUES ('kti001');
INSERT INTO TestTable1 (EmpId) VALUES ('kti002');

Select without DISTINCT
SQL
SELECT EmpId FROM TestTable1 WHERE EmpID in ('kti001','kti001','kti002')

Result
EmpId
------
kti001
kti001
kti002

Select with DISTINCT
SQL
SELECT DISTINCT EmpId FROM TestTable1 WHERE EmpID in ('kti001','kti001','kti002')

Result
EmpId
------
kti001    <-- duplicate removed
kti002

Add a non duplicate row with extra space in the beginning
SQL
INSERT INTO TestTable1 (EmpId) VALUES (' kti001');

Select
SQL
SELECT EmpId FROM TestTable1 WHERE EmpID in ('kti001','kti001','kti002')

Results, the newly added row isn't listed since it does not satisfy the condition
EmpId
------
kti001
kti001
kti002

List the content of the table
SQL
SELECT '>' + EmpId + '<' FROM TestTable1 

Result
(No column name)
----------------
>kti001<
>kti001<
>kti002<
> kti001<

Its impossible to say if an extra space is the problem or something else but the main point is that duplicates aren't removed from the result set without DISTINCT keyword.
   
v3
Comments
souvikcode 13-Jul-15 22:39pm
   
No sir.
When I used or clause and in clause,that eliminates duplicate entry.
select empid from table where empid in ('kti001','kti001','kti002')-only returns data for 'kti001','kti002',ok?
Wendelius 14-Jul-15 1:02am
   
I added an example for you to test. See the modified answer.
souvikcode 14-Jul-15 4:57am
   
Actually as per your example there are two kti001 in table itself.In that case I know that query will return duplicate result(actually not duplicate).
But in my case table consists of unique results.But actually I am sending duplicate value and according to value I need to get empname. That's why I put the question.
I need a datatable returned as

empid name
kti001 abc
ktioo1 abc
ktioo2 cdf

Now is it clear?
Wendelius 14-Jul-15 5:22am
   
I have to admit that I'm now confused. This doesn't match the original question any more...

But let me see, do you mean that you want the query to return non-existent rows based on the condition you have given? For example if your condition (IN clause) contains three values the query should return three rows regardless if they are present in the table?
souvikcode 14-Jul-15 5:38am
   
No.
see I have one row having kti001 in sql table. Right? Now I want to make a datatable in my c# code as I written in above comment.
So if I pass kti001 2 times in sql query , I need to be returned kti001 2 times otherwise if it returns one time then there will be index error in my datatable.
Can I make you understand?
Wendelius 14-Jul-15 5:43am
   
Let me get this. So you have one row in the table. This one row has value 'kti001'

Now you execute a query like
SELECT * FROM MyTable WHERE EmpId IN ('kti001', 'kti001')
and this query should return two rows. Is this what you're after?
souvikcode 14-Jul-15 5:50am
   
yes.you are right.How many items I will pass in "in" query,that same count of rows must be returned irrespective of duplicacy. Now "in" query will not do this job.If you have any other idea,you can tell.
As of now I am creating dynamic query like select * from table where empid='kti001' union all ......
This is serving my purpose.
Wendelius 14-Jul-15 5:55am
   
Ok, what I've been saying is that SQL does not work this way. You cannot generate non-existent rows based on conditions. Conditions can only limit which rows are returned from the table. Conditions cannot create new rows on the fly.

Having that said you need some kind of mechanism to generate rows. If you currently use UNION ALL structure, that's one possibility. However, I would consider using a table valed function to generate the rows. Have a look at Using Table-Valued Functions in SQL Server[^]
souvikcode 14-Jul-15 6:38am
   
Yes.I also think that there is no other way. Ok. Thanks for helping me.
Wendelius 14-Jul-15 6:51am
   
You're welcome.
Deepak Shakti 23-Sep-21 23:01pm
   
Hi @souvikcode, How are you using UNION ALL to serve your purpose.? Could you please provide the query for same example you mentioned?
Wendelius 26-Sep-21 0:14am
   
The best way to get answers is to post a new question at https://www.codeproject.com/Questions/ask.aspx[^]

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900