Left Outer Join with only 1 result

I want to run a sql query using a left outer join, but I only want to return the first result, not all.

Table1:
ParentID 2

Table 2:
ID1 ParentID2
ID2 ParentID2
ID3 ParentId2

So when the rows are joined we have:

ParentID 2& Id1 only, not ID2 and ID3.

How do I do that?

I want to check the null value of the Id1 column to see if there is a record in that table, but I definitely don't want my parent row to show up 3 times, just the once.

thanks.
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
This is what andelIII is trying to show you in his article, you end up with something like this where you use the ROW_NUMBER() function to assign a rank (the row_number) per grouping (the Partition) to your secondary table so that when you join to it, you can match on just the first value.
SELECT * -- Pick the columns you want to see here
FROM dbo.Table1 T
LEFT OUTER JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY ID) rn
				FROM dbo.Table2 T2) T2 ON T.ParentID = T2.ParentID AND rn = 1

Open in new window

You get results like this (I put an ID of 1 with no children to show it works both with and without child records)
Results
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Please view his article 8806085321953
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
that doesn't help.

A DISTINCT won't work because the value of the joined table's primary ID field is going to be unique.

I also don't see how a group by is going to resolve this.

Do you or anyone have any specific solutions to my problem?

thanks.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the "ROWNUMBER()" method will work, and that is exactly what the article is about :)
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I feel I'm on a wild goose chase. I don't find any code in there using a JOIN and the snippet they have for mssql server doesn't show a row number. So unless you can dredge out of that article a snippet for me that works, I'm not closing this.
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Thanks for all the additional effort!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.