Solved

sqlite join with subset from one side of join

Posted on 2014-09-25
5
181 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
  • 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 48

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 41

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now