Make 2 passes at a table in 1 query

[ajhess]

We have a field in our database 'PatientAccount' where the value can represent either a "Hospital" account or a "Professional" account. One or more "Professional" accounts can be linked to a single "Hospital" account, which is defined in a separate field 'HB Match'.

What I would like to do is run a query (SELECT 'PatientAccount', etc...) that would return a list of hospital accounts, and then make a second pass at the same table to see if any of the hospital accounts returned in the 1st pass are in the 'HB Match' field. If yes, I want to return the 'PatientAccount' value associated with that match (which would be any linked "Professional" account(s).

The end result would be a single list of both "Hospital" and any associated "Professional" 'PatientAccount' values. My first thought was to try and run a query and subquery with an 'OR' clause, but that's not working for me...

SELECT 'PatientAccount'
WHERE **criteria**
OR 'HB Match'
IN
(SELECT 'HB Match'
WHERE 'HB Match' = 'PatientAccount'
)

Appreciate any advice, thank you!

 

[bborissov]

Cold you post some example data from both tables and desired result from that data?

Borislav Borissov
VFP9 SP2, SQL Server

 

[ajhess]

Sure! All the data is in the same table... in this sample, 'PatientAccount' 456 and 789 are "Professional" accounts linked to "Hospital" account 123456789.

Patient Account   HB Match    Charges   Payments   DischargeDate
123456789         NULL        100       80          7/1/2013
987654321         NULL        50        50          1/1/2013    
456               123456789   500       200         6/30/2013 
789               123456789   600       400         7/1/2013

I would like to run a query where the 1st pass would pick up accounts w/ 'DischargeDate' = 7/1/2013 AND 'HB Match' = NULL, and then the 2nd pass would pick up any accounts with an 'HB Match' value equal to an account from the 1st pass.

So my desired result would be...

Patient Account     Charges   Payments
123456789           100       80 
456                 500       200
789                 600       400

 

[imex]

Try:

declare @MyTable table
(PatientAccount int, HBMatch int, Charges int, Payments int, DischargeDate date);

insert into @MyTable values
(123456789,         NULL,        100,       80,          '7/1/2013'),
(987654321,         NULL,        50,        50,          '1/1/2013'),    
(456,               123456789,   500,       200,         '6/30/2013'), 
(789,               123456789,   600,       400,         '7/1/2013');

with CTE_Rec as
(
    select 
        PatientAccount,
        Charges,
        Payments
    from @MyTable
    where
        DischargeDate = '7/1/2013' and
        HBMatch is null
        
    union all
    
    select
        t.PatientAccount,
        t.Charges,
        t.Payments
    from CTE_Rec as r
    inner join @MyTable as t
        on t.HBMatch = r.PatientAccount
)
 
select * from CTE_Rec

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[DaveInIowa]

How about

SELECT PatientAccount, Charges, Payments
 FROM [I]YourTable[/I]
WHERE HBMatch IS NOT NULL
   OR PatientAccount IN (SELECT HBMatch FROM [I]YourTable[/I])