Solved

sqlite join with subset from one side of join

Posted on 2014-09-25
5
198 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 250 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 250 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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

696 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