Select query which returns exect no of rows as compare in values of sub query

Question

I have got a table named student. I have written this query:

select * From student where sname in ('rajesh','rohit','rajesh')

In the above query it's returning me two records; one matching 'rajesh' and another matching: 'rohit'.

But i want there to be 3 records: 2 for 'rajesh' and 1 for 'rohit'.

Please provide me some solution or tell me where i am missing.

NOTE: the count of result of sub query is not fix there can be many words there some distinct and some multiple occurrence .

Thanks

Answer 1

just because you've got a criteria in there two times doesn't mean that it will return 1 result per criteria. SQL engines usually just use the unique criteria - thus, from your example, there will be 2 criteria in IN clause: 'rajesh','rohit'

WHY do you need to return 2 results? are there two rajesh in your table? they should BOTH return then. You don't need to ask for rajesh twice for that to happen. What does your data look like? What do you want to see returned?

Answer 2

Try using a JOIN:

SELECT ...
FROM Student s
    INNER JOIN (
        SELECT 'rajesh' AS sname
        UNION ALL
        SELECT 'rohit'
        UNION ALL
        SELECT 'rajesh') t ON s.sname = t.sname

Answer 3

If you can split the where clause in your calling code, you could perform a UNION ALL on each clause.

SELECT * FROM Student WHERE sname = 'rajesh'
UNION ALL SELECT * FROM Student WHERE sname = 'rohit'
UNION ALL SELECT * FROM Student WHERE sname = 'rajesh'

Answer 4

Hi i am query just as you give above and it give me all data that matches in the condition of in clause. just like your post

select * from person where personid in ( 'Carson','Kim','Carson' ) order by FirstName

and its give me all records which fulfill this Criteria

Answer 5

Your requirements are not clear, and I'll try to explain why.

Let's define table students

ID  FirstName   LastName
1   John        Smith   
2   Mike        Smith
3   Ben         Bray
4   John        Bray
5   John        Smith
6   Bill        Lynch
7   Bill        Smith

Query with WHERE clause:

FirstName in ('Mike', 'Ben', 'Mike') 

will return 2 rows only, because it could be rewritten as:

FirstName='Mike' or FirstName='Ben' or FirstName='Mike'

WHERE is filtering clause that just says if existing row satisfy given conditions or not (for each of rows created by FROM clause.

Let's say we have subquery that returns any number of non distinct FirstNames

In case if SQ contains 'Mike', 'Ben', 'Mike' using inner join you can get those 3 rows without problem

Select ST.* from Students ST
Inner Join (Select name from …. <your subquery>)  SQ
On ST.FirstName=SQ.name

Result will be:

ID  FirstName   LastName
2   Mike        Smith
2   Mike        Smith
3   Ben         Bray

Note data are not ordered by order of names returning by SQ. If you want that, SQ should return some ordering number, eg.:

Ord Name
1.  Mike
2.  Ben
3.  Mike

In that case query should be:

Select ST.* from Students ST
Inner Join (Select ord, name from …. <your subquery>)  SQ
On ST.FirstName=SQ.name
Order By SQ.ord

And result:

ID  FirstName   LastName
2   Mike        Smith    (1)
3   Ben         Bray     (2)
2   Mike        Smith    (3)

Now, let's se what will happen if subquery returns

  Ord   Name
   1.   Mike
   2.   Bill
   3.   Mike

You will end up with

ID  FirstName   LastName
2   Mike        Smith   (1)
6   Bill        Lynch   (2)
7   Bill        Smith   (2)
2   Mike        Smith   (3)

Even worse, if you have something like:

Ord  Name
1.   John
2.   Bill
3.   John

Result is:

ID FirstName LastName
1  John      Smith    (1)
4  John      Bray     (1)
5  John      Smith    (1)
6  Bill      Lynch    (2)
7  Bill      Smith    (2)
1  John      Smith    (3)   
4  John      Bray     (3)
5  John      Smith    (3)

This is an complex situation, and you have to clarify precisely what requirement is. If you need only one student with the same name, for each of rows in SQ, you can use something like SQL 2005+):

;With st1 as 
(
Select Row_Number() over (Partition by SQ.ord Order By ID) as rowNum,
       ST.ID,
       ST.FirstName,
       ST.LastName,
       SQ.ord
  from Students ST
 Inner Join (Select ord, name from …. <your subquery>)  SQ
    On ST.FirstName=SQ.name
)
Select ID, FirstName, LastName
  From st1
 Where rowNum=1 -- that was missing row, added later
 Order By ord

It will return (for SQ values John, Bill, John)

ID FirstName LastName
1  John      Smith    (1)
6  Bill      Lynch    (2)
1  John      Smith    (3)

Note, numbers (1),(2),(3) are shown to display value of ord although they are not returned by query.