I need assist sub query

Question

4.00/5 (1 vote)

See more:

i have two table : Tb1,Tb2

TB1 
------------------------
 | ID1| SNAME |
-+----+-------+----------
 | 1  | NAME1 | 
-+----+-------+----------
 | 2  | NAME2 | 
-+----+-------+----------
 | 3  | NAME3 | 
------------------------

TB1
-------------
 | ID1  |   |**___                    TB2
-------------     \___              -----------------------
 | SNAME|   |         \___          |  ID2  |         |   
-------------             |___      +-------+---------+---
                              \==>  | IDTB1 |         |
                                    +-------+---------+---  
                                    |  SID2 |         |
                                    +-------+---------+---
                                    |SVALUE |         |
                                    +-------+---------+-- 

TB2
ID2 IDTB1   SID2   SVALUE

----------------------------------
 |ID2 |IDTB1| SID2 | SVALUE  |
-+----+-----+---------------------
 | 1  |  1  |   5  |   11    |
-+----+-----+--------------------
 | 2  |  1  |   7  |    9    |
-|----|-----+--------------------
 | 3  |  1  |   7  |   10    |
-|----|-----+--------------------
 | 4  |  1  |   7  |   13    |
-|----|-----+--------------------
 | 5  |  2  |   5  |   14    |
-|----|-----+--------------------
 | 6  |  2  |   5  |   16    |
-|----|-----+--------------------
 | 7  |  2  |   5  |   18    |
-|----|-----+--------------------
 | 8  |  2  |   7  |   10    |
-|----|-----+--------------------
 | 9  |  3  |   5  |   10    |
-|----|-----+--------------------
 | 10 |  3  |   1  |   10    |
-|----|-----+--------------------
 | 11 |  3  |   7  |   22    |
---------------------------------



I want query to show results like:
example: where SID2=7

----------------------------
 | ID2 | SNAME | SVALUE |
-+-----+-------+--------+---
 |  4  | NAME1 |   13   |
-+-----+-------+--------+---
 |  8  | NAME2 |   10   |
-+-----+-------+--------+---
 |  11 | NAME3 |   22   |
----------------------------

What I have tried:

SELECT TB1.SNAME,  TB2.ID2,  ( select max(SVALUE) from tb2 where TB2.SID2 = 7 ) as SVALUE FROM dbo.TB1 INNER JOIN dbo.TB2   ON TB1.ID = TB2.IDTB1 WHERE TB2.SID2 = 7

Posted 18-Mar-17 3:22am

spotligh_ly

Updated 18-Mar-17 6:38am

Add a Solution

Comments

[no name] 18-Mar-17 10:05am

SELECT
TB2.ID2,
TB1.SNAME,
TB2.SVALUE
LEFT JOIN TB2 ON TB2.IDTTB1 = TB1.ID1
WHERE TB2.SID2 = '7'

should do it

spotligh_ly 18-Mar-17 16:42pm

Thanks brother /
but I want the return one value IDTB1
When the maximum value SValue or ID2
which easy
When apply your query these results:
----------------------------
| ID2 | SNAME | SVALUE |
-+-----+-------+--------+---
| 2 | NAME1 | 9 |
-+-----+-------+--------+---
| 3 | NAME1 | 10 |
-+-----+-------+--------+---
| 4 | NAME1 | 13 |
-+-----+-------+--------+---
| 8 | NAME2 | 10 |
-+-----+-------+--------+---
| 11 | NAME3 | 22 |
-+-----+-------+--------+---

but i need results like
----------------------------
| ID2 | SNAME | SVALUE |
-+-----+-------+--------+---
| 4 | NAME1 | 13 |
-+-----+-------+--------+---
| 8 | NAME2 | 10 |
-+-----+-------+--------+---
| 11 | NAME3 | 22 |
----------------------------

1 solution

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)

Bryian Tan · Accepted Answer · 2017-03-18T06:38:00

Solution 1

This should do it and I think your sample output is wrong. The max for IDTB1 is 18 and not 10

First select all the max using ROW_NUMBER over function from TB2 then join Tb1

SQL

SELECT  Id2, Sname, SValue
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY [IDTB1] ORDER BY svalue DESC) AS rn
        FROM    dbo.TB2
        ) q
JOIN dbo.TB1
ON q.[IDTB1] = dbo.TB1.[Id1]
WHERE   rn = 1

Output:

Id2	Sname	SValue
4	Name1	13
7	Name2	18
11	Name3	22

Posted 18-Mar-17 6:38am

Bryian Tan

Comments

spotligh_ly 18-Mar-17 17:06pm

Thanks for help ur query it very near what i need ,return max value of (SVALUE)
but you forget there another condition SID2 =value
return max (SVALUE) where SID2 =7 exmpl.

Bryian Tan 18-Mar-17 17:10pm

Try add that filter right after dbo.tbl2

spotligh_ly 18-Mar-17 17:14pm

SELECT
Id2, Sname, SValue
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [IDTB1] ORDER BY svalue DESC) AS rn
FROM dbo.TB2
) q
JOIN dbo.TB1
ON q.IDTB1 = TB1.ID
WHERE rn = 1 and q.SID2=7
but the result
--------------------------------------
Id2 Sname SValue
4 NAME1 13
11 NAME3 22

Bryian Tan 18-Mar-17 17:21pm

Did you try place the filter like this... from dbo.tb2 where sid2= 7

spotligh_ly 18-Mar-17 17:21pm

thanks problem is solved
SELECT
Id2, Sname, SValue
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [IDTB1] ORDER BY svalue DESC) AS rn
FROM dbo.TB2 where SID2=7
) q
JOIN dbo.TB1
ON q.IDTB1 = TB1.ID
WHERE rn = 1

Bryian Tan 18-Mar-17 17:23pm

You're very welcome.