CODEWITHSUNDEEP
sqlsql-server-2008selectsql-order-by
Bhupinder Singh
Bhupinder Singh

Reputation: 1071

Sorting data from one table based on another table

Here are my tables:

create table tableA (id int, type varchar(5))
insert into tableA (ID,TYPE)
values

    (101,'A'),
    (101,'C'),
    (101,'D'),
    (102,'A'),
    (102,'B'),
    (103,'A'),
    (103,'C')

create table tableB (id int, type varchar(5), isActive bit)
insert into tableB (id, type, isActive)
Values 

    (101,'A',1),
    (101,'B',0),
    (101,'C',0),
    (101,'D',1),

    (102,'A',1),
    (102,'B',0),
    (102,'C',1),
    (102,'D',1),

    (103,'A',1),
    (103,'B',1),
    (103,'C',1),
    (103,'D',1)

Now, I want to do two things here: 1) Find the rows that are present in tableA but isActive = 0 in tableB. (done)

select A.* from tableA A
join tableB B
on A.id = B.id and A.type = B.type 
where B.isactive = 0

2) Find the rows that are missing in tableA but isActive = 1 in tableB. For example ID 103, type B is active in tableB but is missing in tableA. I don't care about existance of type D in tableA because I am only checking the last entry in tableA which is C. Also, ABCD is in order for all IDs, they can be active or inactive.

Thanks for your help!

My effort: (not working)

select A.* from tableA A
where exists (
select B.* from tableA A
join tableB B 
on a.id = b.id 
where b.isActive = 1
order by b.id,b.type
)

SQLFiddle

Upvotes: 1

Views: 2102

Answers (1)

Rob Aston
Rob Aston

Reputation: 816

I think you are looking for something like the following:

select B.* from tableB B
left join tableA A
  on B.id = A.id and B.type = A.type
where B.isActive = 1
  and A.id is null
order by B.id, B.type

By using the left join, it means that rows in tableB that have no rows to join with in tableA will have all A.* columns null. This then allows you to add the where clause to check where the tableA records are null thus determining what is contained within tableB that is active and not in tableA

Upvotes: 3

Related Questions