Click here to Skip to main content
15,884,628 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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
 
Share this answer
 
Comments
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!
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
 
Share this answer
 
v2
Comments
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.
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.
 
Share this answer
 
Comments
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
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'

@aa will be your table
 
Share this answer
 
v2

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