Left Outer Joins but only wanting Top1

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

SQL Server Development (2000)

Left Outer Joins but only wanting Top1

Author

Topic

mnish
Starting Member

9 Posts

Posted - 2002-10-24 : 19:46:04

How do you query with a left outer join, but only wanting the top 1 record of the outer joined table and in a specific order?

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-24 : 23:43:10

Have you looked into derived tables? You can embed a select statement into a FROM clause, give it an alias name, and then treat it like it was a table.

for instance


SELECT .....
FROM TableA A LEFT OUTER JOIN
   (SELECT TOP 1 ...... FROM TableB ORDER BY SomeCol) B
  ON (A.KeyCol = B.KeyCol)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Edited by - Lavos on 10/24/2002 23:44:39

mnish
Starting Member

9 Posts

Posted - 2002-10-25 : 02:12:28

I have been trying that (since seeing your reply), but cannot get it to work. It seems that the SELECT TOP 1 will get the top 1 out of the table B, but then there is no matching value to join it to the Table A (because the top 1 from B does not match but one record from A). Am I right in this? Anyways, I cannot get this to work either.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-25 : 19:38:45

Can you sufficiently restrict your data in table B by adding criteria to the JOIN clause like this article talks about?

mnish
Starting Member

9 Posts

Posted - 2002-10-25 : 21:27:51

Here is what I would like to do--let me show you with code that does not work (course, if I could show you with code that DOES work, we wouldn't be wasting our time here, would we?):
SELECT A.ID, A.Desc, B.Date
FROM TableA A
LEFT OUTER JOIN
(SELECT TOP 1 * FROM TableB B
WHERE A.ID = B.ID
ORDER BY A.Date) AS B

I need to show ALL recs of TableA but either none or only one TableB rec per TableA rec. However, TableB can have none, one, or many records linked to a single rec of TableA. So somehow I need to get the TOP 1 TableB rec linked to each TableA rec.

Can't seem to do this, though. Any thoughts?

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-25 : 23:09:28

Let me rephrase this, can you give some example data, and what results you are looking for? Your question isn't completely clear to me, and so.....

I think from my reading of your last post, that you want to get rid of duplicates within the second table, and then join it? That's a tad bit different. You can do a search on the main site for articles on finding duplicates.

Here's a query that will work, but it will be pretty slow. You can find ways to optimize it or replace it with something better.


SELECT A.*, C.*
FROM TableA A LEFT JOIN
 (SELECT B1.*
  FROM TableB B1
  INNER JOIN
    (SELECT ID, MAX(Date) AS Date  FROM TableB GROUP ON ID) B2
     ON (B1.ID = B2.ID AND B1.Date = B2.Date)) C
  ON (A.ID = C.ID)

Change the MAX to MIN or whatever other function to suit your tastes.

Oh yeah, ID and DATE has to be a unique combination, or this won't work for this specific example.

You might be better off with a group by clause in your original query depending on what exactly you need to return.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Edited by - Lavos on 10/25/2002 23:11:24

Edited by - Lavos on 10/25/2002 23:16:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources