Help with 1:M SQL Statement

Transact-SQL
1

Hi,

I have an interesting situation and I am not sure how to handle it. I have two tables, a head and a detail table. There are two types of detail, Corporate and Individual. A header row may have either, neither or both types of detail.

What I need to do is select from the header and get only ONE of the detail rows under the following rule:

  1. If nothing exists, return null values
  2. If only one detail exists, return the one that exists
  3. If there is BOTH a Corporate and Individual detail, return the Corporate one.

I am trying to think of how to best structure this. I was thinking along these lines:

SELECT header.id, detail.foo
  FROM header
  JOIN detail ON header.id = detail.hdrid AND detail.type = 'C'
UNION
SELECT header.id, detail.foo
  FROM header
  JOIN detail ON header.id = detail.hdrid AND detail.type = 'I'
 WHERE header.id NOT IN 
       (SELECT hdrid FROM detail 
        WHERE hrdid = header.id AND detail.type = 'C')
UNION
SELECT header.id, NULL as foo
  FROM header
 WHERE header.id NOT IN 
       (SELECT hdrid FROM detail 
        WHERE hrdid = header.id)

This just seems like an awfully klunky way to go about it. I am open to other thoughts...

Any ideas appreciated

2

This might be simpler

SELECT
	h.id,
	d.foo
FROM
	Header h
	OUTER APPLY
	(
		SELECT TOP (1)
			d.foo
		FROM
			Detail d
		WHERE
			d.hdrid = h.id
		ORDER BY
			CASE WHEN detail.type = 'C' THEN 0 ELSE 1 END
	) d;
3

Nice solution, James. It would be nice if the OP took some time to say thanks.

4

And so he will (and does). With apologies for not having done so sooner but I was away for a little bit.

Thanks

5

Thanks Jeff, kind of you to say. Bazianm, no problem - glad the query was helpful.

6

Thank you and understood. I was a bit sour when I posted my comment because I'd seen so many good posts on several forums with nary a thank you for the people that took the time to help.