how to select one row in joining multiple tables based on a case statement

Question

I have Table A which have reference to Table B. Table B may have one or more entry for each record in A. In B I have a column as status. I want to choose one row by given condition below. Consider I have 3 entries in Table B

If status is OA or OB in any row, then select that row
If status is other than OA or OB then select any one row

My query

--lot more table joins already here
LEFT JOIN(
SELECT CASE WHEN EXISTS
      ( SELECT 1 FROM A a1 INNER JOIN B b1 ON a1.id = b1.id      
        WHERE b1.status in ('OA','OB'))
        THEN (SELECT b1.rcid FROM A a1 INNER JOIN B b1 ON a1.id = b1.id      
        WHERE b1.status in ('OA','OB'))
        ELSE
        SELECT TOP 1 b2.rcid FROM A a2 INNER JOIN B b2 ON a2.id = b2.id
        END
      ))Z on z.id=b2.id --again join with table for b2.rcid

Is this the correct way? Will it have a performance impact?. Really want to highlight here, in real I have to join almost 10 tables of which 5 will have 100 000+ records.

shawnt00 · Accepted Answer · 2016-07-01T19:44:03.207

0

cross/outer apply (
    select top 1 status
    from B b
    where b.id = a.id
    order by case when status in ('OA', 'OB') then 1 else 2 end, status
)

or

inner/left join (
    select
        id,
        case min(case when status in ('OA', 'OB') then 1 else 2 end)
            when 1 then min(cast when status in ('OA', 'OB') then status end)
            when 2 then min(status)
        end
    from B
    group by id
) b on b.id = a.id

edited Jul 01 '16 at 19:44

answered Jul 01 '16 at 19:07

shawnt00

16,443
3
17
22

first solution helps me. But another problem is like, as i mentioned earlier my table A may not contain rows for the tables joined before "APPLY" so this cross apply is skipping those, but i need those records in Table A too. – Satheesh Jul 01 '16 at 19:35
@Satheesh Use `outer apply`. – shawnt00 Jul 01 '16 at 19:37
Yes i tried that and its working, but is it advisable to use outer apply. As per the link http://stackoverflow.com/questions/6726696/cross-apply-vs-outer-apply-speed-difference . will the Outer apply have a performance impact , since am querying a tables with more than a lac records which will pull atleast 10K out of it – Satheesh Jul 01 '16 at 19:41
@Satheesh That's going to depend on a lot of things. You'll have to try it out although I think in your case it won't matter much if any. – shawnt00 Jul 01 '16 at 19:43

how to select one row in joining multiple tables based on a case statement

1 Answers1