SQL query to exclude a result

Question

Hi

I have a query as follows:

SELECT distinct

cast(hratency.cur_tncy_bal as decimal(10,2)),

hratency.tency_seq_no,

                 case when hgmclent.title = '1' then 'Mr'

                       when hgmclent.title = '2' then 'Mrs' 

                       when hgmclent.title = '3' then 'Miss'

                       when hgmclent.title = '4' then 'Master'

                       when hgmclent.title = '5' then 'Ms'

                       when hgmclent.title = '6' then 'Dr'

                       when hgmclent.title = '7' then 'Rev'

                       when hgmclent.title = '8' then 'Sir'

                          else ' '

                              End As salutation,

					   hgmclent.surname,

						case when hgmprty2.con_type = 'HOU' then 'House'

							when hgmprty2.con_type = 'FLA' then 'Main Door Flat'

							when hgmprty2.con_type = 'LCF' then 'Lower Cottage Flat'

							when hgmprty2.con_type = 'MAI' then 'Maisonette'

							when hgmprty2.con_type = 'TEN' then 'Tenement'

							when hgmprty2.con_type = 'UCF' then 'Upper Cottage Flat'

								else ' '

									End As PropertyType,

							hgmclcom.comm_method

FROM

hratency INNER JOIN (hrartamt INNER JOIN (hracracr INNER JOIN hgmclent ON hgmclent."client_no" = hracracr."clnt_no" AND hgmclent."comp_id" = hracracr."comp_id") ON hracracr."rent_acc_no" = hrartamt."rent_acc_no" AND hracracr."comp_id" = hrartamt."comp_id") ON hrartamt."rent_acc_no" = hratency."rent_acc_no" AND hrartamt."comp_id" = hratency."comp_id"

INNER  JOIN hgmprty1 ON hgmprty1.comp_id = hratency.comp_id AND hgmprty1.prty_id = hratency.prty_ref

INNER JOIN hgmprty2 ON hgmprty2.comp_id = hgmprty1.comp_id AND hgmprty1.prty_id = hgmprty2.prty_id

INNER JOIN hgmclcom on hgmclcom.comp_id = hgmclent.comp_id AND hgmclcom.client_no = hgmclent.client_no

WHERE dateadd(day, datediff(day, 0, hratency.tency_st_dt), 0) = dateadd(day, datediff(day, 10, getdate()), 0) and hratency.tency_end_dt is null and hracracr.prim_clnt_yn = 1 

This produces an output of :

(No column name) tency_seq_no salutation surname PropertyType comm_method

260.15 204275 Ms Doherty Tenement EMA

260.15 204275 Ms Doherty Tenement LET

260.15 204275 Ms Doherty Tenement NOSURV

260.15 204275 Ms Doherty Tenement TEL

260.15 204275 Ms Doherty Tenement TXT

290.15 204274 Mr Sliusar Tenement EMA

290.15 204274 Mr Sliusar Tenement LET

290.15 204274 Mr Sliusar Tenement SURVEY

290.15 204274 Mr Sliusar Tenement TEL

290.15 204274 Mr Sliusar Tenement TXT

I have only included the hgmclcom.comm_method while testing as this isn't required in the final script output.

I need the output to exclude the person that has NOSURV included in the hgmclcom table regardless of any other entries.

When i remove the hgmclcom.comm_method column i get the following but need to exclude the Ms Doherty row as she has an entry of NOSURV in the hgmclcom table.

(No column name) tency_seq_no salutation surname PropertyType

260.15 204275 Ms Doherty Tenement

290.15 204274 Mr Sliusar Tenement

Can someone help

Thanks

John

Answer 1

There is no comm_method column in your query? Also, the query is difficult to read with all those nested JOINs on the line after the FROM clause. A further aggravation is that table names are a bit cryptic, making the query even more difficult to understand.

But it sounds like you would need to add a condition to the WHERE clause like:

WHERE NOT EXISTS (SELECT *  
                  FROM   tbl_with_comm_method cm
                  WHERE  cm.user_id = othertable.user_id
                    AND  cm.comm_method = 'NOSURV')

Answer 2

Hi @John Kelly

How about adding this where filter:

WHERE hgmclent.surname IN 
(SELECT hgmclent.surname 
 FROM hgmclent INNER JOIN hgmclcom on hgmclcom.comp_id = hgmclent.comp_id AND hgmclcom.client_no = hgmclent.client_no
 GROUP BY hgmclent.surname
 HAVING SUM(CASE WHEN hgmclcom.comm_method = 'NOSURV' then 1 else 0 end) = 0)

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".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.