Oracle SQL - select only clients with more than one order, listing items from two ID tables, EMAIL and ID_ORDER

Asked

Viewed 284 times

0

I have two tables:

table CUSTOMERS
-----------------------------------
ID_ |   EMAIL
01  |   [email protected]
02  |   [email protected]
03  |   [email protected]


table ORDERS
----------------------------------------------
ID  |   ID_ORDER    |   SKU
01  |   0101        |   123
01  |   0101        |   456
01  |   0102        |   789
02  |   0201        |   124
02  |   0201        |   562
03  |   0301        |   896

I need to select customers with more than one order only, bringing in the report ID, EMAIL of the CUSTOMERS table and ID_ORDER of the ORDERS table. That is, the result should be:

ID  |   EMAIL       |   ID_ORDER

01  |   [email protected]  |   0101
01  |   [email protected]  |   0102

I have tried several ways, and I did not get the necessary result.

sql oracle

  • Tip : Use a subquery, search by EXISTS

  • The HAVING clause can be of great value.

  • The second line of the result should not be: 02 | [email protected] | 0201 ? Or I didn’t understand the relationship between the tables?

  • I needed to list customers with more than 1 order, in case there was only 1, so one line per order. Managed to solve in two ways: SELECT DISTINCT c.ID_, c.EMAIL, o.ID_ORDER FROM CUSTOMERS c inner JOIN ORDERS o ON o.ID = c.ID_ WHERE C.ID_ IN ( SELECT ID FROM ORDERS GROUP BY ID DISTINCT(ID_ODER) > 1 ) OU SELECT DISTINCT c.ID_, c.EMAIL, o.ID_ORDER FROM ( SELECT ID FROM ORDERS GROUP BY ID HAVING COUNT(DISTINCT ID_ORDER) > 1 ) g INNER JOIN C CUSTOMERS ON c.ID_ = g.ID#Xa;INNER JOIN ORDERS o.INNER ID = c.ID = c.ID_&####Xa;; Thanks to you.

1 answer

0

Merge the tables and take only the records that have an order amount greater than 1.

SELECT c.ID, c.EMAIL, o.ID_ORDERS, COUNT(o.ID_ORDER) 
    FROM CUSTOMERS c INNER JOIN ORDERS o ON (c.ID = o.ID) 
    GROUP BY c.ID, c.EMAIL, o.ID_ORDERS 
    HAVING COUNT(o.ID_ORDER) > 1;

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.