CODEWITHSUNDEEP
postgresql
Arturo Verbel De León
Arturo Verbel De León

Reputation: 77

How to make a query with the data of another query on SQL?

I have a user table and another order table. A user can have many orders. How do I get the last 1000 users and those users use my last 1000 orders for each one?

Query - Get users

select distinct users.id, users.first_name, users.last_name
from users
limit 2;

Query - Get orders

select distinct orders.id, orders.user_id
from orders
limit 2;

Upvotes: 0

Views: 65

Answers (1)

dwir182
dwir182

Reputation: 1549

First you need to know.. When it comes to query data don't do separate operation.. If you put out All Users and Orders Your data will be unordered and not consistency.. So you need to make Join to see All User with Order they have.. And as stated by @Sharon you need to add column date_ordered to see that order make.. I am assume you already have that column but i will call that column with date_ordered..

And your query will be :

select
  users.id, 
  users.first_name, 
  users.last_name,
  orders.id
from
  users
  inner join orders on users.id = orders.user_id
order by
  orders.date_ordered desc
limit 1000

By order date_ordered use desc you will get the latest all the user with their order.. And i assume user_id column in table orders have constraint foreign key references to table users with column id..

Upvotes: 1

Related Questions