Simple Sql Query :Problem with query

Question

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 13-Oct-14 0:13am

Member 10995215

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Garth J Lancaster · Answer 1 · 2014-10-13T00:24:00

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

Posted 13-Oct-14 0:24am

Garth J Lancaster

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!

Maciej Los · Answer 2 · 2014-10-13T00:52:00

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

Posted 13-Oct-14 0:52am

Maciej Los

Updated 13-Oct-14 0:55am

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.

Rajesh waran · Answer 3 · 2014-10-13T00:44:00

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.

Posted 13-Oct-14 0:44am

Rajesh waran

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

Arora_Ankit · Answer 4 · 2014-10-13T00:52:00

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

Simple Sql Query :Problem with query

4 solutions

Solution 1

Solution 4

Solution 3

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us