Where is the mistake for this query logic?

Question

I am working on some project and I have 8 tables I created tasks for my self for improving my knowledge and when I'm doing that I had a Problem during query In below there is sample tables and data

CREATE TABLE Clients 
(ID NUMBER(10),
name VARCHAR(30) NOT NULL,
surname VARCHAR(30) NOT NULL, 
Cbudget NUMBER(10,2),
CompanyID NUMBER(10) NOT NULL,
PRIMARY KEY(ID));
Describe Clients

CREATE TABLE Contracts 
(ID NUMBER(20),
ContractValue CHAR(255) NOT NULL,
CompanyID NUMBER(10) NOT NULL,
ClientID NUMBER(10) NOT NULL,
TransactionID NUMBER(10) NOt NULL,
PRIMARY KEY(ID));
Describe Contracts

ALTER TABLE Contracts ADD ClientID NUMBER


CREATE TABLE "Transaction" 
(ID NUMBER(10), 
Type NUMBER(1) NOT NULL,
Price NUMBER(10,2),
TDATE date,
ClientID NUMBER(10) NOT NULL,
PRIMARY KEY(ID));
Describe "Transaction"

here is the data

INSERT INTO Clients(ID,name,surname,Cbudget,CompanyID) VALUES (1,'hamza','bacara',98372.200,2);
INSERT INTO Clients(ID,name,surname,Cbudget,CompanyID) VALUES (2,'','bacara',87432.400,1);
INSERT INTO Clients(ID,name,surname,Cbudget,CompanyID) VALUES (3,'batikan','falay',213132.00,2);

INSERT INTO "Transaction"(ID,Type,Price,TDATE,ClientID)VALUES(1,'1',5000,current_date,2);
INSERT INTO "Transaction"(ID,Type,Price,TDATE,ClientID)VALUES(2,'1',6000,'11/09/2006',1);
INSERT INTO "Transaction"(ID,Type,Price,TDATE,ClientID)VALUES(3,'2',9000,current_date,3);
INSERT INTO Contracts(ID,ContractValue,CompanyID,ClientID,TransactionID)VALUES(1,'1 Million $',1,2,1);
INSERT INTO Contracts(ID,ContractValue,CompanyID,ClientID,TransactionID)VALUES(2,'50 Million $',2,1,2);
INSERT INTO Contracts(ID,ContractValue,CompanyID,ClientID,TransactionID)VALUES(3,'100 Million $',2,2,3);

and here is the query (it works but every time it just shows the data with '100 million $'

SELECT * FROM Contracts WHERE ClientID IN 
(SELECT T.ClientID FROM "Transaction" T 
INNER JOIN Contracts CT ON CT.TransactionID = CT.id WHERE CT.ContractValue = '1 Million $');

I have also constraints (I'm not sure it's important for you)

Does this answer your question? [behavior of inner join inside exists sql](https://stackoverflow.com/questions/65033995/behavior-of-inner-join-inside-exists-sql) — Dornaut, Dec 03 '20 at 17:19
unfortunately, I did not understand that because it has other keywords that I don't know yet. — , Dec 03 '20 at 17:28
All those `varchar` and `char` columns should really be the standard `varchar2`. `varchar` is officially reserved for future use and `char` is [rather specialised](https://stackoverflow.com/a/42165653/230471). — William Robertson, Dec 04 '20 at 20:21

score 0 · Accepted Answer · answered Dec 03 '20 at 17:32

Actually, your query is giving the correct result as per your data. (Only one change in the query ...ON CT.TransactionID = CT.id WHERE... should be ...ON CT.TransactionID = T.id WHERE...)

In the CONTRACTS table, CLIENTID 2 is assigned to two contracts ID = 1 and 3. so the query is matching with two clients.

If you will update the CONTRACTS table as follows then it is giving the correct result.

UPDATE CONTRACTS 
SET CLIENTID = 3 
WHERE ID = 3

db fiddle demo

yeah, I realized that later on but thank you so much ^-^ – Dec 03 '20 at 17:42 — , Dec 03 '20 at 17:42

Where is the mistake for this query logic?

1 Answers1