Getting One Record Per Group From A One-to-Many Join

That UNION ALL trick is rather convenient, isn't it?

How about this one:

SELECT
	c.name,
	p.name AS phone_number,
	p.ext AS phone_ext,
	COALESCE(p.is_preferred, 0) AS is_preferred
FROM
	contact c
LEFT OUTER JOIN
	(
		SELECT
			contact_id,
			COALESCE(
				MIN(CASE WHEN is_preferred = 1 THEN id END),
				MIN(id)
			) AS phoneid
		FROM
			phone
		GROUP BY
			contact_id
	) AS r
ON
	(c.id = r.contact_id)
LEFT OUTER JOIN
	phone AS p
ON
	(r.phoneid = p.id)
ORDER BY
	c.name ASC

Instead of the line-by-line join that yours used, this one tries to grab the best phone number for every contact all at once. The COALESCE() sets up two choices:

1. The first MIN() looks for the first preferred number. It does this just in case someone accidentally ends up with more than one preferred number.
2. The second MIN looks for the first number, whether or not it is preferred.

The "r" subquery then has the best number for each contact, and we join again to [phone] to get the details for the number.

check this 1 out:

select
	c.name,
	case
			(
				select
					count(*)
				from
					@phone p2
				where
					c.id = p2.contact_id
				and
					is_preferred = 1
			)
		when
			0
		then
			(
				select top 1
					p2.name
				from
					@phone p2
				where
					c.id = p2.contact_id
				order by
					p2.id asc
			)
		else
			(
				select top 1
					p2.name
				from
					@phone p2
				where
					c.id = p2.contact_id
				and
					is_preferred = 1
				order by
					p2.id asc
			)
	end as phone_number,
	( p.ext ) as phone_ext,
	(isnull(p.is_preferred,0)) as is_preferred
from
	@contact c
left outer join
	@phone p
on
	c.id = p.contact_id
and
	is_preferred = 1
order by
	c.name asc

Yes, you're right. The extension would require another subquery just like the phone number.

Oh well, thought I'd give it a shot.

P.S. Thanks for formatting.

Peter

Bless you!!!! I needed help with getting the top record only for a join and this helped!

Nice trick, I needed it and found it after a Google search.
Thanks from the Netherlands

Hi,

Is it possible to have the query to return the phone numbers on one line for each worker. Reason I ask, as we have items with multiple prices and management want each item with its' seven different prices on one line. Basically to return the one to many within each line returned.

Most appreciated if you can help. If not, thanks all the same.

Regards, Steve

This is more or less what I am looking for. Once you bring in a second table the SQL code starts to get more complicated.

This should be a big help. Thanks.

Thanks, that got me on the right track :)

the query is fine but not useful for me, if i want result based on some parameter then how can we do this,if add mobile no on your phone table the customer can have multiple phone no and mobile no, i need result based on phone no and mobile no, suppose phone no which i will pass it's match with first row of phone table and mobile no match with second table then how can we get the result?

@Ben

It surprises me how many times I've ended up on your site looking for information. I appreciate your solutions and expertise and I don't even use Cold Fusion. Thanks.

@Manas

If you're storing a mobile AND land line numbers in a table, chances are you don't store both in the same record. You'd normalize and create a "phone_type" field.

Then looking at the example Rick O did with the derived table, you'd take the existing derived table and add a clause that makes it look up only the "Land Line" records. Then you could copy that derived table, give it a different alias and have it only look up the "Mobile" records.

Here's a stab at an example. It appears to work.

SELECT
c.name,
p.name AS phone_land_number,
p.ext AS phone_land_ext,
COALESCE(p.is_preferred, 0) AS is_preferred_land,
p2.name AS phone_mobile_number,
p2.ext AS phone_mobile_ext,
COALESCE(p2.is_preferred, 0) AS is_preferred_mobile
FROM
@contact c
LEFT OUTER JOIN
(
SELECT
contact_id,
COALESCE( MIN(CASE WHEN is_preferred = 1 THEN id END),
MIN(id)
) AS phoneid
FROM
@phone
WHERE phone_type = 'land'
GROUP BY
contact_id
) AS r
ON
(c.id = r.contact_id)
LEFT OUTER JOIN
(
SELECT
contact_id,
COALESCE( MIN(CASE WHEN is_preferred = 1 THEN id END),
MIN(id)
) AS phoneid
FROM
@phone
WHERE phone_type = 'mobile'
GROUP BY
contact_id
) AS mob
ON
(c.id = r.contact_id)
LEFT OUTER JOIN
@phone AS p
ON
(r.phoneid = p.id)
LEFT OUTER JOIN
@phone as p2
ON
(mob.phoneid = p2.id)
ORDER BY
c.name ASC

Very useful article, gave me ideas to solve a similar problem in MySQL.

i really appreciate it, thanks for a great solution.

Hey Ben,

Just needed this exact implementation as I build a homebrew CRM -- so your example was perfect. Once again it pays to read your articles from top to bottom, as you do a great job explaining it all.

Hope all is well with you!

Jon

How about:

select c.name,

<!--- Phone data. --->
( p.name ) AS phone_number,
( p.ext ) AS phone_ext,
(
ISNULL(
p.is_preferred,
0
)
) AS is_preferred
FROM
@contact c
LEFT OUTER JOIN
@phone p
c.id = p.id
group by c.id
order by is_preferred, p.name

Hey Ben,

I was stuck in similar problem nature.
In my database,
Parent Table has 1497039 records
Child Table has 1177925 records

My approach was taking 2 min and 20 sec and after implementing the technique my query ends up in just nano seconds.

Thanks man!

Naveed Ahmad.

Great masters have gathered here. Pupils like me can only put that page into Favorites and mark it "Special" ... and make a back up copy of the Favorites' folder.

Here is a different and more efficient way to do this (from StackOverflow post referenced below (I have not added the additional select * with the appropriate where clause)):

Example:

user1 CategoryA 10
user2 CategoryA 11
user3 CategoryA 9
user4 CategoryB 3
user1 CategoryB 11

the query would return:

Rank User Category
1 user2 CategoryA
2 user1 CategoryA
3 user3 CategoryA
1 user1 CategoryB
2 user4 CategoryB

SELECT
Rank() over (Partition by Category Order by Value, User, Category) as ranks,
Category, User
FROM
Table1
Group By
User, Category, Value
Order by
ranks asc

http://stackoverflow.com/questions/1139719/sql-server-query-for-rank-rownumber-and-groupings