Click here to Skip to main content
15,877,051 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have database like


VB
id=1111 and fighi="1AoHere"

id=3333 and fighi="3AoHere"

id=1111 and fighi="12AoHere"

idl=3333 and fighi="32AoHere"

id=1111 and fighi="11AoHere"

id=3333 and fighi="31AoHere"

id=11121 and fighi="12AoHere"

idl=33332 and fighi="32AoHere"



in datatable

i want to use select query for datatable to get below for query (select id='1111' and id='2222')


VB
1AoHere,12AoHere,11AoHere  all these for id=1111

3AoHere,32AoHere,31AoHere  all these for id =3333
Posted

Try this:
SQL
SELECT Id, fighi=
   STUFF((SELECT ',' + CONVERT(VarChar(10), fighi)
      FROM MyTable b
      WHERE b.Id = a.lId
      FOR XML PATH('')), 1, 1, '')
FROM MyTable a
WHERE Id IN (1111, 3333)
GROUP BY Id
 
Share this answer
 
Comments
maulikshah1990 3-Jan-14 5:02am    
how to use above in asp.net , and in datatable

pls give code example
OriginalGriff 3-Jan-14 5:07am    
Use that as your select query, and it will fill the DataTable.
maulikshah1990 3-Jan-14 5:13am    
1AoHere,12AoHere,11AoHere all these for id=1111

3AoHere,32AoHere,31AoHere all these for id =3333
the above two was just to explain you..

i want values in.. select

exp.: if i want fighi for id=1111 and id=3333 ..what is select query from datatable in asp.net
OriginalGriff 3-Jan-14 5:21am    
Sorry, we seem to be talking at cross purposes here - From what I read of your question the query does exactly what you are asking for - so where is the confusion between us?
Did you try the select? What did it return, and what did you expect it to return?
maulikshah1990 3-Jan-14 5:29am    
forgot everything from before question..
below is what i need to do

i have records in database as


hotelid amenitieid
11123 25253
25361 24452
47458 25253
74852 15236
96585 25253
65923 25253
84664 15236
12321 15236


now from above, if i want hotelid where amenitieid = 25253 and amenitieid =15236 , and not any other hotelid , then what is query in asp.net datatable ...

what too use in datatable.select (amenitieid='15236' and amenitieid='25253');

i hope this is clear ....
 
Share this answer
 
Use thise:
SQL
SELECT ID, FIGHI
FROM TableName
WHERE ID IN (111, 333)

or
SQL
SELECT ID, FIGHI
FROM TableName
WHERE (ID =111) OR (ID =333)


You can't use AND operator with WHERE statement, because ID can't be equal to 111 and 333 at the same time. It can be equal 111 or 333.
 
Share this answer
 

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