CODEWITHSUNDEEP
sqloracle-databasejoin
user2177784
user2177784

Reputation: 3

SQL Select date from second table if id matches

I have two tables Security and CallPut. Security (table1) contain security_id and maturity_date (non noll values) and CallPut(table2) which has security_id and Odate (which are non null values which contains date time format values) I want to select all rows from security(table 1) but in maturity_date i want value from odate column instead of maturity_date where security id exists in Callput(table2) and matches with security table

Eg Security(table1)

Security_id|Maturity_date
abcd        25Jan2020
bcde        25jan2021

Callput(tabble2)

Security_id|Odate
abcd        25Jan2015

Required Output

Security_id|Maturity_date
abcd        25Jan2015 (since here id is there in callput it takes odate)
bcde        25jan2021 (since here id is not there in callput it takes maturity date)

I use oracle sql Please help . Thanks in advance

Upvotes: 0

Views: 563

Answers (3)

roman m
roman m

Reputation: 26531

select s.Security_id, nvl(c.odate, s.Maturity_date) as Maturity_date
from Security s
left join
Callput c
on
s.Security_id = c.Security_id

p.s. just noticed it was an Oracle question, my code is for MS SQL Server
EDIT: corrected for Oracle syntax.

Upvotes: 0

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

select 
    s.Security_id,
    NVL(c.Odate, s.Maturity_date) Maturity_date
from 
    security s left join callput c 
    on (c.security_id = s.security_id);

Upvotes: 1

Corbin Spicer
Corbin Spicer

Reputation: 285

select s.* from security as s left join callput as c on (c.security_id = s.security_id);

Untested but you want to use a join. You can learn more about joins in Oracle's extensive documentation at http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm

Upvotes: 0

Related Questions