Sqlite SQL Statement Not Working As I Expecting

Question

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;

[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) — philipxy, Dec 16 '22 at 10:47
There is no question here. PS Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad definition/query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about bad code 1st because misconceptions get in the way of your goal. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Dec 16 '22 at 10:48
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Dec 16 '22 at 10:49

score 1 · Accepted Answer · answered Dec 15 '22 at 03:47

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`

Sqlite SQL Statement Not Working As I Expecting

1 Answers1