CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Koti Raavi
Koti Raavi

Reputation: 310

compare one row with multiple rows

Ex: I have other main table which is having below data 

Create table dbo.Main_Table
(
ID INT,
SDate Date
)
Insert Into dbo.Main_Table   Values (1,'01/02/2018')
Insert Into dbo.Main_Table   Values (2,'01/30/2018')

Create table dbo.test
(
ID INT,
SDate Date
)
Insert Into dbo.test Values (1,'01/01/2018')
Insert Into dbo.test Values (1,'01/02/2018')
Insert Into dbo.test Values (1,'01/30/2018')

Insert Into dbo.test Values (2,'10/01/2018')
Insert Into dbo.test Values (2,'01/02/2018')
Insert Into dbo.test Values (2,'01/30/2018')

I would like to compare data in main table data with test table. We have to join based on ID and if date match found then "yes" else "No". We have to compare one row with multiple rows.

Please let me know if any questions , thanks for you;re help

Upvotes: 0

Views: 521

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Something like this?

SQL> with main_table (id, sdate) as
  2    (select 1, date '2018-01-02' from dual union all
  3     select 2, date '2018-01-30' from dual union all
  4     select 3, date '2018-07-25' from dual
  5    ),
  6  test_table (id, sdate) as
  7    (select 1, date '2018-01-02' from dual union all
  8     select 2, date '2018-08-30' from dual
  9    )
 10  select m.id,
 11    m.sdate,
 12    case when m.sdate = t.sdate then 'yes' else 'no' end status
 13  from main_table m left join test_table t on t.id = m.id
 14  order by m.id;

        ID SDATE    STATUS
---------- -------- ------
         1 02.01.18 yes
         2 30.01.18 no
         3 25.07.18 no

SQL>

[EDIT, after reading the comment - if you find a match, you don't need that ID at all]

Here you are:

SQL> with test (id, sdate) as
  2    (select 1, date '2018-01-01' from dual union all
  3     select 1, date '2018-01-02' from dual union all
  4     select 1, date '2018-01-30' from dual union all
  5     --
  6     select 2, date '2018-10-01' from dual union all
  7     select 2, date '2018-01-02' from dual union all
  8     select 2, date '2018-01-30' from dual
  9    )
 10  select id, sdate
 11  from test t
 12  where not exists (select null
 13                    from test t1
 14                    where t1.id = t.id
 15                      and t1.sdate = to_date('&par_sdate', 'yyyy-mm-dd'));
Enter value for par_sdate: 2018-01-01

        ID SDATE
---------- ----------
         2 2018-01-30
         2 2018-01-02
         2 2018-10-01

SQL> /
Enter value for par_sdate: 2018-01-02

no rows selected

SQL>

Upvotes: 1

Related Questions