CODEWITHSUNDEEP
sql-server-2008t-sql
slandau
slandau

Reputation: 24052

Joining on another table to remove rows without specific records

So I have a query to filter a bunch of records:

declare @rDate_datepart datetime
-- this gets rid of any time information, so we have just the date (i.e, right at midnight of that day)
set @rDate_datepart = convert(datetime,convert(varchar(2),datepart("m",'2/3/2012'))+'/'+convert(varchar(2),datepart("dd",'2/3/2012'))+'/'+convert(varchar(4),datepart("yyyy",'2/3/2012')))

select tq_tran_quote_id, MAX_MATURITY_DT.cm_maturity_dt
from cfo_tran_quote inner join cfo_transaction
    on tq_tr_transaction_id = tr_transaction_id
left join com_lock on tr_transaction_id = lk_tr_transaction_id
LEFT OUTER JOIN 
(
    SELECT cm_tq_tran_quote_id, MAX(cm_maturity_dt) as 'cm_maturity_dt'
    FROM cfo_component
    GROUP BY cm_tq_tran_quote_id
) AS MAX_MATURITY_DT ON MAX_MATURITY_DT.cm_tq_tran_quote_id = tq_tran_quote_id
where tq_dd_tran_qt_status = 'Matured' and
    isnull(lk_rv_lock_status, 10104) = 10104 and
MAX_MATURITY_DT.cm_maturity_dt between @rDate_datepart - 15 and @rDate_datepart 
and tq_dd_product in 
        (select pr_dd_product 
        from cfo_product 
        where pr_dd_product_type = 'Cash Flow')

Now I basically want to add something to this query that says, give me all of the rows this query brought back where these specific tq_tran_quote_id's do not have records in another table cfo_daily_trans_hist where their dh_valuation_total in that other table is 0.

Does that make sense?

I'm terrible with SQL...lol

Upvotes: 1

Views: 177

Answers (2)

AJP
AJP

Reputation: 2125

select *
from (your query)
where tq_tran_quote_id NOT IN (SELECT tq_tran_quote_id FROM MyOtherTable WHERE dh_valuation_total=0)

good luck

Upvotes: 0

JonH
JonH

Reputation: 33141

All you have to do is take your entire query and add:

WHERE tq_tran_quote_id NOT IN (SELECT tq_tran_quote_id FROM MyOtherTable WHERE dh_valuation_total=0)

Basically it answers your question:

Now I basically want to add something to this query that says, give me all of the rows this query brought back where these specific tq_tran_quote_id's do not have records in another table cfo_daily_trans_hist where their dh_valuation_total in that other table is 0.

It just says WHERE this tq_tran_quote_id is not in the "other table" which you will want to replace MyOtherTable with the real name of your table where their dh_valuation_total is 0.

Upvotes: 2

Related Questions