Oracle SQL - query from one table with two conditions-CodePudding

I need to select data from a table by two conditions

I have four tables

Tran

tranid	payed
123	yes
456	yes
789	no

Info

tranid	contractname	amount	currency	accountid
123	finance	10	EUR	12345
123	card	8	USD	54321
456	finance	20	EUR	67890
456	card	16	USD	09876
789	finance	30	EUR	13579
789	card	24	USD	97531

Account

accountid	currencyid
12345	124
54321	978
67890	124
09876	840
13579	124
97531	826

Currency

currencyid	currencyname
978	EUR
840	USD
826	GBP
124	CAD

I need to extract: tran id where payed = yes, amount where contract name = finance and account currency name where contact name = card

I have tried to make join with multiple select, but I always have error: ORA-01427: single-row subquery returns more than one row

select t.tranid,
   (select i.amount
    from tranid t
    inner join info i on t.tranid = i.tranid
    and t.payed = 'yes'
    and i.contractname = 'finance') as "Amount",
c.currencyname as "Account currency"
from tran t
inner join info i on t.tranid = i.tranid
inner join account a on i.accountid = a.accountid
inner join currency c on a.currencyid= c.currencyid
and t.payed = 'yes'
and i.contractname = 'card'

Result must be:

tranid	Amount	Account currency
123	10	EUR
456	20	USD

CodePudding user response：

The issue here is that your subquery is not correlated with your main query - it doesn't reference any of the outer query tables. If you run it by itself, you'll notice that it returns 2 rows.

select i.amount
from tranid t
inner join info i on t.tranid = i.tranid
and t.payed = 'yes'
and i.contractname = 'finance'

Also, when you reference the same table multiple times in a query, it's good practice to use different aliases for each one, so you can tell them apart.

Here's a relatively small change that should make your query work:

select t.tranid,
    (select i2.amount
    from info i2 
    where i2.tranid = i.tranid
    and i2.contractname = 'finance') as "Amount",
c.currencyname as "Account currency"
from tran t
inner join info i on t.tranid = i.tranid
inner join account a on i.accountid = a.accountid
inner join currency c on a.currencyid= c.currencyid
and t.payed = 'yes'
and i.contractname = 'card'

Although if it were me, I wouldn't use a subquery at all - I'd just join the info table twice.

select t.tranid,
    i2.amount as "Amount",
c.currencyname as "Account currency"
from tran t
inner join info i on t.tranid = i.tranid
    and i.contractname = 'card'
inner join info i2 on t.tranid = i2.tranid
    and i2.contractname = 'finance'
inner join account a on i.accountid = a.accountid
inner join currency c on a.currencyid= c.currencyid
and t.payed = 'yes'

I don't really understand your currency column in the info table, it doesn't seem to match up with the currency table. But I guess it's not relevant to the question.