Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

INNER JOIN on TOP 1 Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2008 8:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:15 PM
Points: 292, Visits: 767
Good Morning, everyone:

I'm trying to join two tables.
However, I only want to return the TOP 1 row from the child table.

As a simplified example, consider the following two tables (I've included the code to create the temp tables and data below):

TableA
=====
RetailerID
54
33


TableB
=====
RetailerID, RetailerIDTheirs
54, 'ABC'
54, 'DEF'
33, 'TUV'
33, 'WXYZ'


I would like to know how to write the JOIN syntax to return only the TOP 1 row from TableB.

The only solution that I know how to do is something like this:

SELECT
A.RetailerID,
(SELECT TOP 1 RetailerIDTheirs FROM TableB WHERE RetailerID = A.RetailerID ORDER BY RetailerIDTheirs ASC) AS RetailerIDTheirs
FROM TableA AS A

Which returns exactly what I want:


This works fine, except that when I try to group the data, I get an error message telling me:
"Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause."

For the purpose of keeping things simple, I've left-out the part of the code that includes the grouping.

As always, thanks to everyone for any thoughts/input.

-Simon

Code for creating sample tables and rows:
-- Create TableA, the Parent table
CREATE TABLE TableA
(RetailerID INT NOT NULL PRIMARY KEY CLUSTERED)

-- Populate TableA
INSERT TableA (RetailerID)
VALUES (54)

INSERT TableA (RetailerID)
VALUES (33)


-- Create TableB, the Child table
CREATE TABLE TableB
(
RetailerID INT NOT NULL
CONSTRAINT FK_TableB_RetailerID FOREIGN KEY ([RetailerID]) REFERENCES TableA ([RetailerID]),
RetailerIDTheirs VARCHAR(10) NOT NULL
)

-- Populate TableB
INSERT TableB (RetailerID, RetailerIDTheirs)
VALUES (54, 'ABC')

INSERT TableB (RetailerID, RetailerIDTheirs)
VALUES (54, 'DEF')

INSERT TableB (RetailerID, RetailerIDTheirs)
VALUES (33, 'TUV')

INSERT TableB (RetailerID, RetailerIDTheirs)
VALUES (33, 'WXYZ')



  Post Attachments 
TSQL_Select_Top1.jpg (7 views, 18.64 KB)
Post #522569
Posted Tuesday, June 24, 2008 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,234, Visits: 13,719
Hi Simon

This is simple but somewhat restrictive:

SELECT A.RetailerID, MIN(B.RetailerIDTheirs) AS RetailerIDTheirs
FROM #TableA A
LEFT JOIN #TableB B ON B.RetailerID = A.RetailerID
GROUP BY A.RetailerID

This is less simple but offers more flexibility:

SELECT A.RetailerID, B2.*
FROM #TableA A
LEFT JOIN (SELECT RetailerID, MIN(RetailerIDTheirs) AS RetailerIDTheirs FROM #TableB GROUP BY RetailerID) B ON B.RetailerID = A.RetailerID
LEFT JOIN #TableB B2 ON B2.RetailerID = B.RetailerID AND B2.RetailerIDTheirs = B.RetailerIDTheirs

Both are likely to be more efficient than a correlated subquery.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #522584
Posted Tuesday, June 24, 2008 8:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Another alternative


SELECT
A.RetailerID,
X.RetailerIDTheirs
FROM TableA AS A
CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B WHERE B.RetailerID = A.RetailerID ORDER BY B.RetailerIDTheirs ASC) AS X



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #522588
Posted Tuesday, June 24, 2008 8:34 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:15 PM
Points: 292, Visits: 767
Mark and Chris,

Thank you so much for your replies.
I'll try both and let you know what works best.

Thanks again - I really appreciate it.

-Simon
Post #522590
Posted Tuesday, June 24, 2008 8:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,234, Visits: 13,719
Mark (6/24/2008)
Another alternative


SELECT
A.RetailerID,
X.RetailerIDTheirs
FROM TableA AS A
CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B WHERE B.RetailerID = A.RetailerID ORDER BY B.RetailerIDTheirs ASC) AS X



Nice one, Mark, and far closer to the OP's requirements. We're still on 2k here.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #522591
Posted Tuesday, June 24, 2008 9:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 15,663, Visits: 28,061
This will work with 2000:

SELECT
A.RetailerID,
b.RetailerIDTheirs
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.RetailerID = B.RetailerID
AND b.RetailerIDTheirs = (SELECT TOP 1 B2.RetailerIDTheirs
FROM TableB B2
WHERE B2.RetailerID = A.RetailerID
ORDER BY B2.RetailerIDTheirs ASC)



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #522634
Posted Tuesday, June 24, 2008 3:47 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:15 PM
Points: 292, Visits: 767
Just as a follow up, Mark I used your CROSS APPLY syntax and it worked perfectly - allowing me all the groupings I needed.
Thanks again everyone for the expert help.

-Simon
Post #522926
Posted Wednesday, June 25, 2008 6:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455
Dear Friend,

I think the simplest query for your question is as mentioned below:

-------------------------------------------------------------
Select T2.RetailerID, T2.RetailerIDTheirs
From TableA T1
Inner Join
(Select *,Rank() Over (Partition By RetailerID Order by RetailerIDTheirs) As Rank from TableB) T2
ON T1.RetailerID=T2.RetailerID
Where Rank=1


Cheers,
Hari
Tips & Tricks for SQL BI Developers
Post #523203
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse