I'm trying to put together a query which allows a user to input any of the id's and return any record in relation to that family.

Output I'd like to achieve

Example 1: If I query person_id '100', It would return the self, spouse, and child with the member_id 'abc'

Example 2: external_id '321' would return all member_id and family for 'def'

This is what I've put together so far, but it's limited to a single ID.

select *
from table
    where member_id in (
        select member_id
            from table
        having count (distinct external_id) > 1
        )
    and external_id = '123'

CodePudding user response：

Try this. It uses inner join on member_id and a sub query with the search parameters. Substitue the hard-coded '100' and '123' for a variable (i.e. t2.person_id = @search_id).

select distinct t.*
from table as t
    INNER JOIN table as t2
        ON t2.member_id = t.member_id
        and (
            t2.person_id = '100'
            OR t2.external_id = '123'
        )

Your query would probably work if you moved the and external_id = '123' inside the parentheses.

select *
from table
    where member_id in (
        select member_id
        from table
        where external_id = '123'
    )

CodePudding user response：

Basically, you want to find:

1. The rows representing @person_id or external_id (assuming you would pass one parameter or the other)
2. The rows in the same member_id as the rows representing that same @person_id or external_id

Since both sets have the same member_id (it is not hierarchical), we can do this with:

WITH cte AS
(
  SELECT TOP (1) member_id
    FROM dbo.table 
    WHERE person_id = @person_id
       OR external_id = @external_id
    ORDER BY [key_column]
)
SELECT <cols> FROM dbo.table AS t 
WHERE EXISTS
(
  SELECT 1 FROM cte WHERE member_id = t.member_id
);

Working example in this fiddle.