Sqlite SQL Statement Not Working As I Expecting-CodePudding

Im using sqlite3 database and i try to get data from two table with cross join. They have some foreign keys and i can`t get reference value for currency names (b_currency and s_currency).

They need to be like 'USD','EUR','TRL' etc.

SQL STATEMENT:

select 
a.pid, 
person.fullname,
a.amount as b_amount,
b.amount as s_amount,
a.currency as b_currency,
b.currency as s_currency,
a.rate as b_rate,
b.rate as s_rate,
`user`.username,
a.`date`
from buy_process as a
inner join person
on a.fullname=person.id
inner join  currency
on  b_currency=currency.id and s_currency=currency.id
inner join  `user` 
on a.`user`=`user`.id
cross join sell_process as b
where a.pid=b.pid;

BUY_PROCESS AND SELL_PROCESS TABLE FIELDS ARE SAME:

-- Describe BUY_PROCESS
CREATE TABLE `buy_process`(
    id integer primary key AUTOINCREMENT,
    pid VARCHAR(50) NOT NULL UNIQUE,
    fullname INTEGER NOT NULL,
    amount VARCHAR(50) NOT NULL,
    currency INTEGER NOT NULL,
    rate VARCHAR(50) NOT NULL,
    `user` INTEGER NOT NULL,
    `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fkbuy_fullname FOREIGN KEY(fullname) REFERENCES person(id),
    CONSTRAINT fkbuy_currency FOREIGN KEY(currency) REFERENCES currency(id),
    CONSTRAINT fkbuy_user FOREIGN KEY(`user`) REFERENCES `user`(id)
);

RESULT:

Result image

I tried to change field names but i did not succeed:

a.pid, 
person.fullname,
a.amount as b_amount,
b.amount,
currency.name as b_currency,
currency.name as s_currency,
a.rate as b_rate,
b.rate as s_rate,
`user`.username,
a.`date`
from buy_process as a
inner join person
on a.fullname=person.id
inner join  currency
on  b_currency=currency.id and s_currency=currency.id
inner join  `user` 
on a.`user`=`user`.id
cross join sell_process as b
where a.pid=b.pid;

CodePudding user response：

I don't understand what you want to achieve with the cross join (you have a condition a.pid=b.pid, why not just inner join them?).

You need to join the currency table twice, once for the buy currency, and once for the sell currency:

select 
   ...
   b_cncy.name as b_currency,
   s_cncy.name as s_currency,
   ...
from 
   buy_process as bp

   inner join 
   sell_process as sp
   on bp.pid=sp.pid
   
   inner join 
   currency b_cncy
   on b_cncy.id=bp.currency

   inner join currency s_cncy
   on s_cncy.id=sp.currency

   inner join `user` usr
   on usr.id=bp.`user`