June 8, 2015 at 1:03 pm
I have a two tables each having a uniqueidentifier column person_id
I am trying to a select statement where I want a list of the person_id's in one table that are not in another table.
-- insert into wch_needed those who need checked
insert into #wch_needed (person_id, rendered_by )
select distinct e.person_id, e.rendered_by
from #wch_who o, encounter e
where o.person_id not in (select distinct person_id from #wch_have )
and o.person_id = e.person_id
the where conditional
where o.person_id not in (select distinct person_id from #wch_have )
does not work.
How can I do this?
Thanks in advance for your time Dean-O
June 8, 2015 at 1:20 pm
try this:
select column1 from table1
except
select column1 from table2
this will give all the values in column1 in table1 that are not in cloumn1 of table2.
--Happy coding
June 8, 2015 at 2:50 pm
There are a couple reasons this might happen.
First, if person_id in the table in the subquery is NULL for any row, then no rows will be returned when using NOT IN. Gail Shaw has a nice piece on NOT IN and NOT EXISTS here: http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/.
Second, if the column in the table in the subquery is actually named something other than person_id, then that reference to person_id in the subquery will bind to person_id in the table referenced in the outer query instead. In that case, it will also understandably return no rows.
That feature is a bit sneaky, since you might expect that to complain about an invalid column, but it won't so long as a person_id column exists in one of the tables referenced in the outer query. That's one reason it's generally good to prefix column names with a table name or alias, especially in subqueries.
I'd check to make sure that neither of those are the case.
If you have NULLs, then in some cases you might be able to use EXCEPT, as SolveSQL suggested. In this particular case, though, that wouldn't work, as EXCEPT requires the two queries to have the same number of columns/expressions.
If the table referenced in the NOT IN subquery has NULLs, then you'd probably want to just use NOT EXISTS instead of NOT IN.
That would look something like this:
insert into #wch_needed (person_id, rendered_by )
select distinct e.person_id, e.rendered_by
from #wch_who o, encounter e
where NOT EXISTS (select h.person_id from #wch_have h WHERE h.person_id=e.person_id)
and o.person_id = e.person_id
Cheers!
EDIT: I updated this to give an example of how NOT EXISTS would be used in this particular case. I had meant to include that initially but submitted instead of previewing.
June 8, 2015 at 3:50 pm
...
where o.person_id not in (select distinct person_id from #wch_have where person_id is not null )
...
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 9, 2015 at 7:56 am
SSCrazy thanks... that seemed to do the trick.
Dean-O
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply