?
Solved

sqlite join with subset from one side of join

Posted on 2014-09-25
5
Medium Priority
?
201 Views
Last Modified: 2014-10-01
Looking at these example tables stoler here.

sqlite> SELECT * FROM Customers;
CustomerId  Name       
----------  -----------
1           Paul Novak 
2           Terry Neils
3           Jack Fonda 
4           Tom Willis 

sqlite> SELECT * FROM Reservations;
Id  CustomerId  Day       
--  ----------  ----------
1   1           2009-22-11
2   2           2009-28-11
3   2           2009-29-11
4   1           2009-29-11
5   3           2009-02-12

Open in new window


I'm looking for a query which gives me a join of the two tables (customers and reservations), but in the result I only need one reservation day for each customer, and I need to be able to choose which one (oldest, youngest).

So normal inner join would give this result:
Paul Novak   2009-22-11 
Terry Neils  2009-28-11 
Terry Neils  2009-29-11 
Paul Novak   2009-29-11 
Jack Fonda   2009-02-12 

Open in new window


Where I need this result:
Paul Novak   2009-22-11 
Terry Neils  2009-29-11 
Paul Novak   2009-29-11 
Jack Fonda   2009-02-12 

Open in new window


Or this result, depending on whether I choose youngest or oldest reservation day
Paul Novak   2009-22-11 
Terry Neils  2009-28-11 
Paul Novak   2009-29-11 
Jack Fonda   2009-02-12 

Open in new window




Thanks.
0
Comment
Question by:MerijnB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 19

Accepted Solution

by:
MerijnB earned 0 total points
ID: 40345043
After digging around more it seems I get the proper result with the following query:
select customers.name, max(reservations.day) from customers join reservations on customers.CustomerID = reservations.CustomerID group by customers.CustomerID

Open in new window


I can switch to youngest / oldest by using max() or min().

Is this the best / most efficient way?
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 total points
ID: 40345175
Is this the best / most efficient way?

Yes, it is a good approach for this need.
0
 
LVL 42

Assisted Solution

by:pcelba
pcelba earned 1000 total points
ID: 40345682
Just one question:
Is your output sample correct?
Paul Novak   2009-22-11 
Terry Neils  2009-28-11 
Paul Novak   2009-29-11 
Jack Fonda   2009-02-12 

Open in new window

Paul Novak is displayed twice...

Real life will also require to display the nearest reservation which is not in the past. In such case simply add WHERE clause:

WHERE Reservations.Day >= DATE()

And yes, you should select your post as the answer :-)
0
 
LVL 19

Author Comment

by:MerijnB
ID: 40345704
Ouch, you're so right pcelba, Paul Novak should be displayed just once.
the where clause you mentioned isn't applicable, but that's just because the data I showed here is a simple representation of my situation.

Thanks for confirming both!
0
 
LVL 19

Author Closing Comment

by:MerijnB
ID: 40354159
Turns out to be the way to go
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

762 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