Click here to Skip to main content
15,041,788 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi guys, i have an issue with sql query:

i have 2 tables, and i want table1 left join table2, mission is to show all table1's rows and for each table1's row, i search records in all table2 until the 1st matched value.

so
CSS
results' row number = talbe1's row number
, just add table2's 1st matched value, but here i get
CSS
results' row number >talbe1's row number
, any help, plz.*
Posted
Comments
pradiprenushe 11-Sep-14 22:22pm
   
provide query.

Hi,

Check this Left Outer Join[^]

Hope this will help you.

Cheers
   
Comments
v03051435 11-Sep-14 5:18am
   
thx, but i kown what left join is, just i have an issue with my question abouve.
Magic Wonder 11-Sep-14 5:22am
   
Yeah i know but if you will not share the complete details with some sample data if possible then how can be able to help you with your exact problem?
Something like:
SQL
SELECT * FROM TableA
LEFT OUTER JOIN 
(SELECT relevantField, ROWNUMBER() OVER(PARTITON BY ID ORDER BY RelevantField2 DESC) RN
 FROM TableB) b
ON TableA.Field = TableB.Field
WHERE b.RN = 1

Without knowing the table structure, you have to put in the correct field names yourself, but the idea is that adding a ROWNUMBER to the second table in the subquery allows you to select only the first record resulting from the join.
   
Comments
kbrandwijk 11-Sep-14 9:19am
   
Why is a working solution downvoted...

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