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:
- If nothing exists, return null values
- If only one detail exists, return the one that exists
- 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