Member Avatar for xXghostXx

I want to select persons who has orders =<150
tried this query but of course it wrong anyone can point me to the correct one ?

using Oracle or SQL

select firstname,cost from persons,orders where cost=100 group by firstname



CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
FirstName varchar(255),
LastName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT 'unknown'
);

CREATE TABLE Orders
(
O_Id int NOT NULL,
P_Id int,
OrderName varchar(255),
cost int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

insert into persons values ('1','sara','lala','street10','de');
insert into persons values ('2','toto','no','street10','nl');
insert into persons values ('3','momo','yes','street10','ch');
insert into persons values ('4','bobo','yea','street10','sy');
insert into persons values ('5','malk','tata','street10','sy');
insert into persons (P_Id,FirstName,LastName,Address) values ('6','yuo','zoo','street10');


insert into Orders values ('1','1','Order1','100');
insert into Orders values ('2','1','Order2','150');
insert into Orders values ('3','2','Order3','120');
insert into Orders values ('4','3','Order4','200');
insert into Orders values ('5','3','Order5','220');
insert into Orders values ('6','3','Order6','100');
insert into Orders (O_Id,OrderName) values ('7','Order7');
insert into Orders (O_Id,OrderName) values ('8','Order8');
insert into Orders (O_Id,OrderName) values ('9','Order9');
Edited by xXghostXx
  • 3 Contributors
  • 5 Replies
  • 290 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by xXghostXx

Dani AI

Generated

This thread mixes three different meanings of “orders <= 150” and that’s why the SQL variants behave differently. As noted, the tables must be joined; as pointed out, use the ANSI JOIN ... ON form rather than writing INNER JOIN and putting the join condition in WHERE. Also, ’s use of HAVING was the right direction when totals are required — the exact clause depends on whether the goal is per-order, per-person total, or “every order” constraints.

To pick persons whose total orders are at most 150 (aggregate per person) without accidental grouping by name, a correlated subquery avoids grouping-by-name pitfalls and works across Oracle/SQL Server/MySQL:

SELECT p.firstname
FROM persons p
WHERE (SELECT COALESCE(SUM(o.cost),0) FROM orders o WHERE o.p_id = p.p_id) <= 150;

To select persons for whom every order is <= 150 (no single order above 150), aggregate and test the maximum. Using LEFT JOIN includes people with no orders (treating them as max = 0):

SELECT p.p_id, p.firstname
FROM persons p
LEFT JOIN orders o ON o.p_id = p.p_id
GROUP BY p.p_id, p.firstname
HAVING COALESCE(MAX(o.cost),0) <= 150;

Practical notes: group by the primary key (P_ID) plus any non-aggregated columns to avoid merging different people who share a firstname; use COALESCE/NVL to handle NULLs (Oracle: NVL, SQL Server: ISNULL); and ensure orders.p_id is indexed for performance. Also confirm cost is a numeric column (avoid quoted numeric literals) to prevent implicit conversions and slow plans.

Recommended Answers

All 5 Replies

Member Avatar for pritaeas

You need to join both tables in the where.

Member Avatar for xXghostXx

these two queries works on MySQ only
on oralce & MS SQL there is syntax near the keyword 'where' i dont know what is it can you help ?

select persons.firstname,orders.cost from persons inner join orders where persons.p_id=orders.p_id and cost<=150



select persons.firstname,orders.cost from persons inner join orders where persons.p_id=orders.p_id and (cost between 0 and 150)

Thanks for help

Edited by xXghostXx
Member Avatar for urtrivedi

ON keyword is missing in your code

select persons.firstname,orders.cost from 
persons inner join orders on  persons.p_id=orders.p_id 
where (cost between 0 and 150)
Member Avatar for xXghostXx

***thanks ! that what pressure coz

Member Avatar for xXghostXx

here is if there are two orders made by same persosn

SELECT persons.firstname,sum(orders.cost)
FROM persons INNER JOIN orders ON persons.p_id=orders.p_id
group by firstname 
having sum(orders.cost)<=250
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.