0

I'm very new to SQL and I've been searching for an answer for a while now, can't seem to find it.

I have 1 table of CustOrders which has 3 columns.

Table CustOrders

  • Name (varchar)
  • Number_Orders (int)
  • order_date (datetime)

Question

I would like to get a list of customers that have had less orders than a specific customer 'x'. How would I go about doing that?

Select Statement

I tried with this logic, which didn't seem to work and is probably all wrong, which was:

SELECT name, number_orders
FROM custOrders
WHERE number_orders < (select number_orders from custOrders where name = 'x');

It returned an empty set, which should not be empty, because I know there are customers there with less orders.

Could you please help me with this?

I am using MariaDB.

0
SELECT t1.*
FROM custOrders t1, custOrders t2 
WHERE t1.number_orders < t2.number_orders 
  AND t2.name = 'x';

PS. custOrders.name must be defined as unique by according index.

2
  • Hi Akina, thank you so much! This works now. Do you maybe know how to make it display only the names? Right now it displays the whole table. I tried with SELECT name, t1.* (got abiguous error), I tried t1.name, didn't work sitll displayed everything, tried custOrders.name, got a syntax error. Thanks in advance and sorry for asking so many questions! – Banananas Feb 25 '20 at 19:48
  • @Banananas how to make it display only the names? Replace SELECT t1.* FROM ... with SELECT t1.name FROM .... – Akina Feb 26 '20 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.