15,884,628 members
See more:
below is query i am stuck with how to do

sno |smanid | sid
------------------
1 |5 |4
2 |5 |2
3 |6 |7
4 |3 |5
5 |6 |3
smanid is product made using sid

smanid 5 is made from sid 4 and 2
smanid 6 is made from sid 7 and 3

now give product is, sid i want to know smanid

eg : given 4 and 2 gives 5
given 7 and 3 gives 6

how can i write query for that i am stuced
Posted

## Solution 1

Im not sure what your issue is - surely its just

SQL
select smanid from yourtable where sid = yourvalue

where 'yourtable' should be the real name of your table and yourvalue is 4 or 2 or 7 or 3 as you want

Member 10995215 13-Oct-14 6:28am
no give 4 and 2 it should give 5
Garth J Lancaster 13-Oct-14 6:37am
so are you trying to tell me if you do

select smanid from yourtable where sid = 4

you don't get 5 ?
Member 10995215 13-Oct-14 6:39am
no given is case select smanid which has value for sid as 4 and 2 both
Rajesh waran 13-Oct-14 6:46am
try solution 3
Maciej Los 13-Oct-14 6:46am
5!

## Solution 4

I'm not sure what you want to achieve, but it sounds like you want to concatenate rows into one row[^], which will give you result like:
5 | 4,2
etc.

SQL
SELECT DISTINCT smanid, STUFF((SELECT ','+ CONVERT(VARCHAR(30), sid)
FROM [YourTable] AS S2
WHERE S1.smanid = S2.smanid
FOR XML PATH('')), 1, 1, '') AS Sids
FROM [YourTable] AS S1

v2
Member 10995215 13-Oct-14 7:03am
not works from mysql ??
Maciej Los 13-Oct-14 7:06am
You haven't tagged question properly. This solution is for MS SQL server.

## Solution 3

If passing 2 id values are static,u can try this,

select distinct smanid from table where sid=value1 and sid=value2

apart from this u can use "and,or" as per your needs.

Arora_Ankit 13-Oct-14 6:53am
the query you gave will not work with and
Rajesh waran 13-Oct-14 7:04am
s right,then y don't u go with ("or") case

## Solution 5

SQL
SELECT
qry.smanid
FROM
(
SELECT
smanid,
sids =
REPLACE(STUFF((
SELECT
', ' + CAST(b.sid AS varchar)
FROM
@aa b
WHERE
b.smanid = a.smanid
FOR xml PATH ('')), 1, 2, ''), ' ', '')
FROM
@aa a
GROUP BY
a.smanid) qry
WHERE
qry.sids = '4,2'