0

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
6
Views
4 Years
Discussion Span
Last Post by xXghostXx
0

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

1

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)
0

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.