Zahid Zahid - 3 months ago 11
SQL Question

Any other way to solve this query?

Table: Customer

customer_id cust_name city grade salesman_id
----------- ------------ ---------- ---------- -----------
3002 Nick Rimando New York 100 5001
3005 Graham Zusi California 200 5002
3001 Brad Guzan London 5005
3004 Fabian Johns Paris 300 5006
3007 Brad Davis New York 200 5001
3009 Geoff Camero Berlin 100 5003
3008 Julian Green London 300 5002
3003 Jozy Altidor Moncow 200 5007


Table: Order

ord_no purch_amt ord_date customer_id salesman_id
---------- ---------- ---------- ----------- -----------
70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001


I need a query that produces the name and number of each customer with more than one current order.

My try:-

SELECT customer_id, cust_name
FROM customer a
WHERE (SELECT COUNT (*)
FROM orders b
WHERE a.customer_id = b.customer_id) > 1


This gives desired output. But, is there any alternate way which would give desired output and perform better than this?Additionally,how could I know which one will do better?

Answer

I'd try this:

SELECT customer_id,  cust_name
FROM customer a
WHERE customer_id IN (SELECT customer_id
                      FROM orders b
                      GROUP BY customer_id
                      HAVING COUNT(*) > 1)

The sub-query simply returns the customer_id's that exists more one once in the orders table.

Or this, similar to the above, but a JOIN instead of IN:

SELECT a.customer_id, a.cust_name
FROM customer a
JOIN (SELECT customer_id
      FROM orders b
      GROUP BY customer_id
      HAVING COUNT(*) > 1) b ON a.customer_id = b.customer_id