Returning a Single Row in Left Join

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 2005 Forums

Transact-SQL (2005)

Returning a Single Row in Left Join

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2009-01-08 : 12:38:31

I bet this is an easy one - but not an easy one to locate in a search engine, apparently. The following statement may return from 0 to infinity rows - but I don't really care - if it returns more than 1 row, I just want it to return the first one found - the same as if I had deleted all the other rows, leaving it with just one row.

LEFT JOIN dbo.tblTESTTABLE v ON H.TransId=v.TransId AND v.RowStatus = 'ACTIVE'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 12:47:54

[code]SELECT TOP 1...
FROM...
LEFT JOIN dbo.tblTESTTABLE v ON H.TransId=v.TransId AND v.RowStatus = 'ACTIVE'
ORDER BY ...[/code]

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-08 : 13:01:57

quote:

Originally posted by visakh16

SELECT TOP 1...
FROM...
LEFT JOIN dbo.tblTESTTABLE v ON H.TransId=v.TransId AND v.RowStatus = 'ACTIVE'
ORDER BY ...

I don't think he wants to select the top 1 row - he wants his JOIN to return the 1st row.

BobRoberts
Posting Yak Master

109 Posts

Posted - 2009-01-08 : 13:12:59

visakh16 - You're making an assumption that my question is within the wider context that you supply, "SELECT...ORDER BY", but I specified the context for my question. You see, it is part of a huge complicated SELECT statement in a software package. Above are many CASE, IF THEN, statements, directly above are 3 LEFT JOIN statements. I want to leave the other logic alone - I'm afraid I'll have an unintended effect on that logic. All my little LEFT JOIN statement does is get a piece of information that appears on the top of an invoice form - but its result, if there is more than one row in the file it is "joining" for that bit of info, is that the detail lines on the invoice are doubled, tripled, quadrupled, if 2, 3, or 4 matches are found in the tblTESTTABLE file.

I'll experiment with putting "TOP" to the right of the SELECT statement way above, and see what that does. But it isn't clear from your example what the "ORDER BY" is for or what its parameters should be, or even if its required to accomplish what I need to.

BobRoberts
Posting Yak Master

109 Posts

Posted - 2009-01-08 : 13:16:51

Yes, Skorch, you are correct. I don't really understand what this big complicated SELECT statement is doing, or how it is doing it - I only know that adding my little LEFT JOIN at the end of the other 3 previous LEFT JOIN'S (and these are before the WHRERE) causing info to get into the variable that displays on the heading of the invoice.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-08 : 13:25:31

I think this could possibly accomplish what you're looking to do:

LEFT JOIN dbo.tblTESTTABLE v ON H.TransId=(SELECT TOP 1 TransId FROM dbo.tblTESTTABLE WHERE RowStatus = 'ACTIVE')

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 13:37:27

quote:
Originally posted by rohitkumar

[quote]Originally posted by BobRoberts

I bet this is an easy one - but not an easy one to locate in a search engine, apparently. The following statement may return from 0 to infinity rows - but I don't really care - if it returns more than 1 row, I just want it to return the first one found - the same as if I had deleted all the other rows, leaving it with just one row.

LEFT JOIN dbo.tblTESTTABLE v ON H.TransId=v.TransId AND v.RowStatus = 'ACTIVE'


LEFT JOIN
(SELECT *
FROM   (SELECT *,
               ROW_NUMBER()
                 OVER(PARTITION BY Transid ORDER BY Transid) AS Seq
        FROM   Dbo.Tbltesttable WHERE RowStatus = 'ACTIVE') a
WHERE  Seq = 1) v
ON H.TransId=v.TransId

BobRoberts
Posting Yak Master

109 Posts

Posted - 2009-01-08 : 14:50:49

I think this could possibly accomplish what you're looking to do:

LEFT JOIN dbo.tblTESTTABLE v ON H.TransId=(SELECT TOP 1 TransId FROM dbo.tblTESTTABLE WHERE RowStatus = 'ACTIVE')

I don't understand how the "TransId" to the right of the "1" relates to the statement. What does it do? Along with the rest, does that statement also accomplish the equality condition of H.TransId=v.TransId?

I ran it with your suggestion, and it did the same thing (too many detail lines). I changed "TOP 1 TransID" to "TOP 1 v.bitofinfo", and it printed the right number of lines - but didn't come up with any v.bitofinfo to print on the heading of the invoice!

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 15:05:48

and did you try this?


LEFT JOIN
(SELECT *
FROM   (SELECT *,
               ROW_NUMBER()
                 OVER(PARTITION BY Transid ORDER BY Transid) AS Seq
        FROM   Dbo.Tbltesttable WHERE RowStatus = 'ACTIVE') a
WHERE  Seq = 1) v
ON H.TransId=v.TransId

BobRoberts
Posting Yak Master

109 Posts

Posted - 2009-01-08 : 15:16:18

Thank you, rohitkumar - you are a SQL god.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources