How to use SQL query to get earliest Date from multiple Tables?

Question

Summary (Revised on 10 Jan 2016): I need info from 3 Tables (A, B, C), TableC has no primary key, hence I will get multiple [C_Date] record for each [B_ID].

' Description of Database:
'    A Ms Access database file: "c:\DB\Data.mdb"
'    It has 3 Tables: [TableA], [TableB], [TableC]
'    TableA with Fields [A_ID], [A_Design]
'    TableB with Fields [B_ID], [A_ID], [B_InventoryNum], [B_BlankNum] 
'    TableC with Fields [B_ID], [C_Name], [C_Value], [C_Date] 
'    TableA Primary Key is [A_ID]
'    TableB Primary Key is [B_ID]
'    TableC has no Primary Key, hence, for each [B_ID], there are multiple [C_Date]

Below is one example of the data from TableC
B_ID    Name    Value       C_Date
73    Diamter    35.375     27-Jan-98
73    Diamter    35.376     27-Jan-98
73    Diamter    35.375     12-Apr-98
73    Diamter    35.374     19-Jul-98
73    Diamter    35.374     23-Sep-98
73    Diamter    35.374     30-Mar-99
73    Diamter    35.375     24-Oct-99
73    Diamter    35.374     24-Oct-99
73    Height     22.491     27-Jan-98
73    Height     21.908     12-Apr-98
73    Height     21.908     19-Jul-98
73    Height     21.915     23-Sep-98
73    Height     21.901     30-Mar-99
73    Height     21.909     24-Oct-99
73    Height     22.041     27-Jan-98

The Query Sequence is as follow:

1. Use where [A_Design] LIKE '%99%' to Get [A_ID] From TableA  
2. Use 'Left Join' to link to TableB
3. Use 'Left Join' again to link to TableC

'Left Join' is just an example, it can be replaced by 'Inner Join'.

My current Sql query will return all the 15 rows for a B_ID (73), but I just need a single date - the earliest date (27-Jan-98), I am also not interested in other fields, such as [Name], [Value].

The following codes in VB.NET will retrieve multiple [C_Date]. If I only want the earliest [C_Date], how shall I modify my SQL script? Any suggestion and feedback would be greatly appreciated!

If you are not interested in VB.NET, please jump directly to Sql string, the question is for SQL syntax, which is independent from any specific programming language.

Many thanks to @PhilipXY and @Rory for the suggestion to test only within the Access environment, example codes and informative guides, finally my problem got solved.

Below SQL is a working copy from the MS Access SQL View of a Query.

SELECT Distinct [TableA].[A_Design], [TableA].A_ID, [TableB].B_ID, [TableB].B_InventoryNum, [TableB].[B_BlankNum], [TableC].C_Date, [TableC].B_ID FROM ([TableA] INNER JOIN [TableB] ON [TableA].A_ID = [TableB].B_ID) INNER JOIN [TableC] ON [TableB].B_ID = [TableC].B_ID GROUP BY [TableA].[A_Design], [TableA].A_ID, [TableB].B_ID, [TableB].B_InventoryNum, [TableB].[B_BlankNum], [TableC].C_Date, [TableC].B_ID HAVING ((([TableA].[A_Design]) Like '99') AND (([TableC].C_Date)=(SELECT TOP 1 Min([TableC].C_Date) FROM [TableC] Where [TableC].B_ID=[TableB].B_ID)));

With this revised SQL, now I am able to retrieve the following info from TableC:

B_ID    C_Date
73    27-Jan-98

Problem solved! I would like to vote 5 for both @PhilipXY and @Rory. ( I assume that 5 is the highest point I can vote.)

VB.NET Codes:    
================================================================
    Imports System.Data.OleDb
    Imports System.Windows.Forms
    ' 1. Just add a DataGridView1 control on your winform,
    ' 2. then add a button (Named: 'btnTest')
    ' 3. Below is the button click handler, it will retrieve info from database and display it on the "DataGridView1" control.

Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click

    Dim MDBConnString_ As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\Data.mdb;"
    Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
    cnn.Open()

    Dim Sql As String
    Sql = "SELECT " & "([A_Design]+Format([B_InventoryNum],""0000"")) AS DesignCodePK, [TableA].[A_ID], [A_Design], [TableB].[B_ID], [B_InventoryNum], [B_BlankNum], [C_Date] FROM ([TableA] LEFT JOIN [TableB] On [TableA].[A_ID] = [TableB].[A_ID]) " & " LEFT JOIN [TableC] On [TableB].[B_ID] = [TableC].[B_ID] Where [A_Design] LIKE '%99%' Order by [A_Design], [B_InventoryNum], [C_Date] "

    Dim cmd As New OleDbCommand(Sql, cnn)
    Dim DataAdapter As New OleDbDataAdapter(cmd)
    Dim ds As System.Data.DataSet
    ds = New System.Data.DataSet

    DataAdapter = New OleDbDataAdapter(cmd)
    DataAdapter.Fill(ds, "joined")
    DataGridView1.DataSource = ds.Tables("joined")
    cnn.Close()
End Sub

If the question is about a query then you should simply ask that in the question. What query are you looking for? — Shawn, Jan 09 '16 at 06:48
@philipxy TableC has 4 fields in total ( [B_ID], [C_Name], [C_Value], [C_Date]). By left join from TableB, I got the [B_ID]. By using [B_ID], I can get multiple [C_Date], but I only need the earliest [C_Date], I try to use Min([C_Date]) without any luck. — Wayne, Jan 09 '16 at 07:46
@Shawn I am looking for a query to only return the earliest date ([C_Date] field will return multiple records). Please refer to my question post for detail info, thank you for your response. — Wayne, Jan 09 '16 at 07:53
@philipxy To be more precise, I don't really know how to use Min() in my Query, can you help to modify the Sql string to add in the Min()? Thanks. — Wayne, Jan 09 '16 at 08:09
You seem to want to return only one row per B_ID? Ie if you were to GROUP BY B_ID then for each group of rows with a given B_ID you would want the one row HAVING the MIN C_DATE? — philipxy, Jan 09 '16 at 09:07
@philipxy Sorry, overlooked your question. In fact, (B_ID, C_Date) pair is not unique. That's why I think of the Min() function, which will give me only one row. You are right, one row is enough for me. But I tried many way to insert the Min() function into Sql string, none of them works. — Wayne, Jan 09 '16 at 09:26
@philipxy To be precise, it was not added by Access, rather it was forced to add into the "HAVING" clause, because whenever I tried to add the "Min" function, Error message "Cannot have aggregate function in WHERE clause . (Error 3096)" appeared. But if I add the Min() part into "HAVING", the SQL string was accepted. — Wayne, Jan 10 '16 at 08:23
@philipxy What I am doing in access is simple: Use Query Design, add 3 Tables, Select all the necessary fields from 3 Tables, Write Criteria under fields. For example: Under [A_Design] field, I write Like '*99*', Switch to SQL view, I can get the SQL Script. But in design view you can not do sth. like Rory's example, so I switch to the SQL view and change the string directly. — Wayne, Jan 10 '16 at 08:31
But the Access has its own rules, my changes always got rejected. It's really a painful try and error process. — Wayne, Jan 10 '16 at 08:37

Rory · Accepted Answer · 2016-01-10T20:21:31.690

2

To get the min date for a B_ID use

select min(c_date) from TableC where b_id = XXXX

To get all the min dates

select b_id, min(c_date) as MinDate
from TableC group by b_id

You can then join that query on to other tables:

select *
from TableB
left join (
    select b_id, min(c_date) as MinDate
    from TableC group by b_id
) as minimumDates
    on minimumDates.b_id = TableB.b_id

If you're having trouble with query syntax try to run your SQL against MS Access directly rather than via VB. That'll make it easier to check what does/doesn't work. If you can't get the GROUP BY to work then start with a new query as simple as possible, like with my first select above. Forget which columns you really want, just get a simple query working and then add JOINs and WHERE and columns one at a time.

edited Jan 10 '16 at 20:21

answered Jan 09 '16 at 10:34

Rory

40,559
52
175
261

Thanks for your answer. I tried below Sql, it did not work. Sql = "SELECT " & "([A_Design]+Format([B_InventoryNum],""0000"")) AS DesignCodePK, [TableA].[A_ID], [A_Design], [TableB].[B_ID], [B_InventoryNum], [B_BlankNum], Min([C_Date]) FROM ([TableA] LEFT JOIN [TableB] On [TableA].[A_ID] = [TableB].[A_ID]) " & " LEFT JOIN [TableC] On [TableB].[B_ID] = [TableC].[B_ID] Where [A_Design] LIKE '%99%' Order by [A_Design], [B_InventoryNum], [C_Date] " – Wayne Jan 09 '16 at 10:40
Here is the error message: {"You tried to execute a query that does not include the specified expression '[A_Design]+Format([B_InventoryNum],"0000")' as part of an aggregate function."} – Wayne Jan 09 '16 at 10:50
Maybe it was overlooked, it is there just before the Keyword "From". Min([C_Date]) FROM – Wayne Jan 09 '16 at 12:14
Yes, I tried to insert "Group By" many places in the Sql string, but none of them worked. Can you help to modify this Sql? Thanks. – Wayne Jan 09 '16 at 12:32
1

I'm not very familiar with Access, but I suggest you forget about your VB code for a moment and just run SQL through MS Access directly. That way you can more easily change things and see the results. You also remove the string-formatting `"` and `&` that gets in the way of readabiliy. Nicer-formatted SQL really does help to understand why a query is/isn't working. – Rory Jan 09 '16 at 15:37
1

For your updated SQL query, you _need_ the `GROUP BY` to be able to use the aggregate function `MIN()`. Start by simplifying the query as much as possible to get the `MIN()` and `GROUP BY` working and then build it up. – Rory Jan 09 '16 at 15:41
@WayneJ. Besides simplifying per Rory's comments, use the nested select with MIN & GROUP BY the way his example does. – philipxy Jan 09 '16 at 18:45
Thank you very much for all the comments and help, I have revised my questions and SQL, now I am able to reduce the repeat records from 15 to 2, how can I reduce to 1 record only? – Wayne Jan 10 '16 at 02:40
@philipxy 1. Now I am using the Ms Access Toos to create the SQL string, I try to follow exactly as Rory's method without success. And "HAVING" is not from my own alteration, it is added by Access automatically. 2. Just tried "SELECT DISTINCT" keyword, the result are still duplicated (2 records). 3. I am also not familiar with Access, will keep trying, will get you notified if I ever get any satisfied result. Thanks. – Wayne Jan 10 '16 at 07:40
Thank Rory and Philipxy, by playing around with the DISTINCT, finally I am able to get the single record (no duplication). I am revising the question part now. The comments from both of you are really helpful to guide me to the solution. By the way, how do I mark the status of this question as solved and how to vote for people who is helping? – Wayne Jan 10 '16 at 10:54
@WayneJ. - *"how do I mark the status of this question as solved"* - You do that by [accepting](http://meta.stackexchange.com/a/5235/238021) an answer. – Gord Thompson Jan 10 '16 at 12:45
GordThompson Thanks, I have already accepted the answer and voted for Gory. Too bad, I am only allowed to vote for 1 person. Any way, thank @Philipsxy also for your help. – Wayne Jan 10 '16 at 12:59
Hi Rory, just a side question: If in TableA, I use where [A_Design] in ("A", "D", "B", "C") to query the record, how can I make sure the retrieved records also follow the same sequence as ("A", "D", "B", "C") ? Thanks. – Wayne Jan 10 '16 at 13:48
2

@WayneJ. - Unrelated follow-up questions are discouraged here. If you have another question then [ask a new question](http://stackoverflow.com/questions/ask). You can always include a [link to this question](http://stackoverflow.com/q/34690526/2144390) in your other question to provide context. – Gord Thompson Jan 10 '16 at 15:44
@WayneJ. - short answer: you can't. Longer answer: ask a new question, link to it from a comment here, and you'll get more explanation. – Rory Jan 10 '16 at 20:20
Hi Rory, thanks for the response, I have already asked a new question and included a link to here. – Wayne Jan 11 '16 at 00:20

How to use SQL query to get earliest Date from multiple Tables?

1 Answers1

Linked