Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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.
0
MerijnB
Asked:
MerijnB
  • 3
3 Solutions
 
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
 
PortletPaulCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now