-1

I have one table called table1. This table has three columns column1 and column2 and dates_tart. Columns column1,column2 has same date type (int) and date_start has date type (date). For example, I'm adding a screenshot of this table (MSSQL).

Id  column1  column2  date_start
--  -------  -------  ----------
1   1        1        2019-01-20
2   2        2        2019-01-21
3   3        4        2019-02-15
4   4        3        2019-02-17
5   5        6        2019-03-20
6   7        7        2019-03-20
7   8        9        2019-03-20
8   9        8        2019-04-02
9   10       12       2019-04-06
10  14       13       2019-04-07
11  15       0        2019-04-08
12  16       NULL     2019-04-10

My desired result:

1/ I need a list of numbers that are in one column but not present in the next column (for example: number 5 is in column1 but not in column2 similarly number 12 is in column2 and not in column1). And the resulting values must be in April 2019

2/ I need a list of numbers that are in one column but not present in the next column (for example: number 5 or 10 is in column1 but not in column2) - So result will only contain records from column1 that are not in column2

Thank you!

  • 3
    Welcome to DBA.SE, always include DDL statement (as mentioned in answer) part of the question, consider this post for your future question.. – Shekar Kola Nov 12 at 5:59
  • Also please avoid pictures of text. Tabular data may be a little trickier to post but you can format it as code and then it's easy to arrange nicely, see how I did it with your data sample. Cheers. – Andriy M Nov 12 at 10:55
4

A FULL OUTER JOIN would be one way:

create table table1 (column1 int, column2 int null)

insert into table1
values
(1,1),
(2,3),
(3,2),
(10,12),
(5,10)

select *
from table1 t1
full outer join table1 t2
on t1.column1 = t2.column2
where t1.column1 is null or t2.column2 is null

Or wrap that in EXISTS to get it as a single set.

select x.* from table1 x
where exists(
select 1
from table1 t1
full outer join table1 t2
on t1.column1 = t2.column2
where (t1.column1 is null or t2.column2 is null)
and (x.column1 = t1.column1 or x.column2 = t2.column2))

Or using two exists

select *
from table1 t1
where not exists (select 1 from table1 c1 where t1.column1 = c1.column2)
   or not exists (select 1 from table1 c2 where t1.column2 = c2.column1)

Or as a single column, of only the numbers.

select t1.column1
from table1 t1
where not exists (select 1 from table1 c1 where t1.column1 = c1.column2)
union 
select t1.column2
from table1 t1
where not exists (select 1 from table1 c2 where t1.column2 = c2.column1
  • I have one more question: If I have next "create_date" column in this table that has a data type (date) and I will want to filter the records displayed by some date (between ‘2019-10-01’ and ‘2019-10-31’), how do I put this condition exactly in your statements? Thank you some much for help – H0nzin Nov 12 at 11:21
  • Just add it to the where clauses. – scsimon Nov 12 at 14:07
  • 1 / I used your third option (two exists). Here I have tried to add other conditions, but result was still same 2 / Can you still advise on how to modify your scripts so that each row in column [column1] is compared to all rows in column [column2], but the result will only contain records from “column1” that are not in [column2]? – H0nzin Nov 12 at 17:48
  • I don't understand your issue. You mentioned a date field that wasn't in the sample data. If you update your post with new sample data and expected results, it would be easier to help you out. I can't determine why you aren't getting correct results otherwise, as you see the sample data and scripts i provided work as expected. – scsimon Nov 12 at 17:51
  • Sorry about that, i updated my post – H0nzin Nov 12 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.