?
Solved

Help with LEFT OUTER JOIN

Posted on 2014-08-05
4
Medium Priority
?
254 Views
Last Modified: 2014-08-05
Hi
I have a Customer tables and an Orders table.

How can I write a query that will return just 1 record from the Orders table

SELECT * FROM CUSTOMERS A
  LEFT OUTER JOIN ORDERS ON A.CUST_ID = ORDERS.CUST_ID
0
Comment
Question by:JElster
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40242618
Which order do you need? The latest order? This query will return you the latest order:
SELECT * FROM CUSTOMERS A
  LEFT OUTER JOIN 
  ORDERS O ON A.CUST_ID = O.CUST_ID 
    AND O.ORDER_ID = (SELECT MAX(ORDER_ID) FROM ORDERS WHERE CUST_ID = O.CUST_ID)

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 40242628
order by most current ORDER DATE.
So I need the customer and most current order
0
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40242648
This will retrieve the most current orders. However, please note that is it is possible that there are a few orders generated at the same time for the same customer, there could be duplications. Please check it against your database and advise if there are any duplications:
SELECT * FROM CUSTOMERS A
  LEFT OUTER JOIN 
  ORDERS O ON A.CUST_ID = O.CUST_ID 
    AND O.ORDER_DATE = (SELECT MAX(ORDER_DATE) FROM ORDERS WHERE CUST_ID = O.CUST_ID)

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 40242663
THX
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question