sqlite join with subset from one side of join

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.
LVL 19
MerijnBSr. Software EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MerijnBSr. Software EngineerAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Is this the best / most efficient way?

Yes, it is a good approach for this need.
0
pcelbaCommented:
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
MerijnBSr. Software EngineerAuthor Commented:
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
MerijnBSr. Software EngineerAuthor Commented:
Turns out to be the way to go
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.