SQL Select subquery using exists

Question

I am trying to write a query with a subquery. The subquery returns what is in the attached image below.

select top(1) with ties matter_uno, PART_CAT_CODE, EFF_DATE, EMPL_UNO from TBM_CLMAT_PART where MATTER_UNO = 11275 and PART_CAT_CODE = 'BILL' order by eff_date desc

Note: Most of the time, only 1 row is returned by this subquery, but sometimes, more than 1 row can be returned.

All good so far. Now, I want to take this query and add it a main query. It will sort of look like a 1 to 1 or 1 to possibly many query, again, if more than 1 row is returned as below. I can't use the In operator as I need my subquery to return more than 1 field, so I am trying the Exists operator. But when I use the Exists operator in my query, only the record with empl_uno of 2979 is returned.

This is the complete query I am using below. Hbm_Matter will only 1 have unique record(matter_uno). How do I get multiple records returned in my query when there are 2 records in the subquery?

select matter_uno, matter_name, bill_empl_uno from hbm_matter a where exists ( select top(1) with ties matter_uno, PART_CAT_CODE, EFF_DATE, EMPL_UNO from TBM_CLMAT_PART where matter_uno = a.MATTER_UNO and PART_CAT_CODE = 'BILL' order by eff_date desc) and status_code = 'O' and inactive = 'N' and MATTER_UNO = 11275 order by a.MATTER_UNO

Answer 1

This is a bit of a guessing game, but it sounds like this what you are looking for:

select matter_uno, matter_name, bill_empl_uno 
from  hbm_matter a 
cross apply  ( select top(1) with ties matter_uno, PART_CAT_CODE, EFF_DATE, EMPL_UNO 
               from TBM_CLMAT_PART 
               where matter_uno = a.MATTER_UNO 
                 and PART_CAT_CODE = 'BILL' 
               order by eff_date desc) as b
where status_code = 'O' 
  and inactive = 'N' 
  and MATTER_UNO = 11275 
order by a.MATTER_UNO

If this is off the mark, I think it helps if you post CREATE TABLE statements for your tables, INSERT statements with sample data and the desired result given the sample, so we can develop a tested solution.

Answer 2

a subquery only returns one column (only true/false if used in "exists"). if not used in an "in" clause, returning more than 1 row is an error. if you need multiple columns or rows, then use a join.

Answer 3

Hi @Joseph Mancini

I tried a few other things with no luck, but I am missing something and am not sure what.

Your left join query seems no syntax error.

Have you check if remove where m.BILL_EMPL_UNO = 2979 will work? I suspect it is the where condition that filters out the result set of the subquery。

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".