best way to join when you only want 1 record

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

Transact-SQL (2012)

best way to join when you only want 1 record

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2015-01-21 : 07:34:13

I often have this come up where I want to do a left join but i want to see all the records from the first table and only 1 record from the second

so the that total count will be same even if the table that is being joined has more then 1 match

what is the best way to do this?

CleaningWoman
Starting Member

13 Posts

Posted - 2015-01-21 : 08:37:26

It sounds more like a union all than a left join.

Can you give an example?

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-21 : 08:48:25

It has choice to eliminate more than one record in second table like
1. Use Sub Query to distinct the second table records and take only one record per combination
2. Use for xml to combine more than one record to single row

Regards
Viggneshwar A

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2015-01-21 : 09:47:37

how would I do it with a subquery

maybe what I want is a union - i'm not sure

I have customers and one customer can have more then 1 claim

I want to show customers and then I want to show the first claim

what's the best way to do this?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-21 : 10:14:41

First, you need to answer a question:

if the left join produces 1 record for the first table and 2 for the second table, how will you choose which row of the second table to return?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2015-01-21 : 10:54:22

i guess the last one would be best

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-21 : 11:41:00

well, what do you mean by last one? Remember a query returns a set. by definition a set has no ordering. So, the last row returned today may not be the last row returned tomorrow. In other words, you need to define what columns to sort on. To get the last one, it'll be something like:

select top(1) ...order by <orderingcolumn> desc

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-21 : 16:39:44

Use OUTER APPLY:


SELECT table1...., oa1....
FROM table1
OUTER APPLY (
    SELECT TOP (1) ...
    FROM table2
    WHERE table2.lookup_val = table1.lookup_val
    --ORDER BY table2....
) AS oa1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources