Query join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query join

Author

Topic

Codesearcher
Starting Member

8 Posts

Posted - 2011-12-07 : 00:00:52

I have two Queries

Query A returns
OPERPROP   WELLCOMPL      WELLNAME             RESNO
6521 2162911 ST205 B-02 4075
6521 2355711 ST206 B-03 4075
6521 2106801 ST205 B-01 4077
1234 2222222 SOB-2509-01 7122

Query B returns
WELLCOMPL RESName
1234567 A
2222222 B

Logic needed
Compare the WellCompl no in Query A with Well compl no from Query B
for OperProp 6521 i dont have any matching record so final query should return the last record of this group
when it reads wellCompl no 2222222 it has a match so the final query should return its matching record fro query B

Final result should be

OPERPROP WELLCOMPL WELLNAME    RESNO   ResName
6521 2106801 ST205 B-01 4077 NULL
1234 2222222 SOB-2509-01 7122 B

How to implement this logic in SQL query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 00:08:12

[code]
SELECT OPERPROP, WELLCOMPL, WELLNAME, RESNO, ResName
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY a.OPERPROP,b.ResName ORDER BY a.WELLCOMPL DESC) AS Rn,
a.OPERPROP, a.WELLCOMPL, a.WELLNAME, a.RESNO, b.ResName
FROM (QueryA)a
LEFT JOIN (QueryB)b
ON b.WELLCOMPL = a.WELLCOMPL
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-07 : 00:19:34

how do you match the result of Query A to result of Query B ? using which column of A to match with which column of B ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources