Cross Reference Columns ORACLE SQL

Question

I am having trouble selecting all values from a table. I need to select all vehicles where their vRentTimes value is the same as the times they appear in the Renting table. So basically just checking if the recordings of rentings are correct.

This is the description of tables:

Vehicle

Name         Null?    Type         
------------ -------- ------------ 
VPLATENUMBER NOT NULL VARCHAR2(7)  
VCOLOR       NOT NULL VARCHAR2(10) 
VCC          NOT NULL NUMBER       
VHORSEPOWER  NOT NULL NUMBER       
VRENTTIMES            NUMBER       
VEHCATNAME   NOT NULL VARCHAR2(20)

Renting

Name         Null?    Type        
------------ -------- ----------- 
CAFM         NOT NULL VARCHAR2(9) 
VPLATENUMBER NOT NULL VARCHAR2(7) 
OUTDATE      NOT NULL DATE        
INDATE                DATE

user_1144035 · Answer

I see. vRentTimes is supposed to be a count for the second table. You can use JOIN and `aggregation. To get the rows that do not match:

select v.*, r.cnt
from vehicles v left join
     (select VPLATENUMBER, count(*) as cnt
      from renting r
      group by VPLATENUMBER
     ) r
     on r.VPLATENUMBER = v.VPLATENUMBER
where v.VRENTTIMES <> coalesce(cnt, 0);

Cross Reference Columns ORACLE SQL

1 Answers