i have query retrieves documents users have assigned them. there users missing documents retrieve documents not exist in application users need have these docs completed. following query stuck on not exists part. query not return records , know of these users not have document
select t.prac_id, t.document_id, t.documentname, s.doc_id **#tmp_prcs t-- table contains doucments users have** left outer join **#tmp_doclookup s -- doc table contains userid , doc id.** on t.prac_id = s.prac_id not exists (select doc_id #tmp_prcs l s.prac_id = l.prac_id) order prac_id, document_id
the where
turning left join inner join. add condition join's on
clause instead.
select t.prac_id, t.document_id, t.documentname, s.doc_id #tmp_prcs t left outer join #tmp_doclookup s on t.prac_id = s.prac_id , not exists ( -- use "and" here instead of "where" select doc_id #tmp_prcs l s.prac_id = l.prac_id ) order prac_id, document_id
Comments
Post a Comment