Query left join

[Anetal]

Hi,

I have a problem. I need to join two tables with a query. A need all information from table 1 and Value for every ID from table 2 - but only from max available year.
How to do it?

Table 1

id	name
1​	A
2​	B
3​	C
4​	D
5​	E

Table 2

id	year	value
1​	2020​	100​
1​	2019​	50​
1​	2018​	0​
2​	2019​	200​
2​	2018​	100​
3​	2015​	300​

 

[onur_can]

Then your Table2 should be the one on the left. In this case, you must add Year to Table1 and create 1 to many relations in between.

 

[isladogs]

I'm obviously reading your post in a different way to @onur_can as its clear to me that Table1 (all records from...) is on the left side of the join.
I think this is what you want:

SELECT Table1.ID, Table1.name, Max(Table2.Year) AS MaxOfyear, First(Table2.Value) AS FirstOfValue
FROM Table1 LEFT JOIN Table2 ON (Table1.ID = Table2.id)
GROUP BY Table1.ID, Table1.name;

However, your query criteria aren't clear.
If that doesn't give the expected results, please can you state what results your query should have based on that data.

Also Name, Year, Value are all reserved words and should not be used as field names

 

[Anetal]

I'm obviously reading your post in a different way to @onur_can as its clear to me that Table1 (all records from...) is on the left side of the join.
I think this is what you want:

SELECT Table1.ID, Table1.name, Max(Table2.Year) AS MaxOfyear, First(Table2.Value) AS FirstOfValue
FROM Table1 LEFT JOIN Table2 ON (Table1.ID = Table2.id)
GROUP BY Table1.ID, Table1.name;

However, your query criteria aren't clear.
If that doesn't give the expected results, please can you state what results your query should have based on that data.

Also Name, Year, Value are all reserved words and should not be used as field names

Thank you very much.


I tried the sample data method but got the wrong result. I am attaching a file

 

[isladogs]

I can't test yours as you are using linked Excel tables which obviously won't work for me.
I've attached the test db I created before answering. The results I get, using your original data are:

You modified your original data after posting...

 

[Anetal]

For id=2 , max year should be 2020 and value for this year 1000.
In query year is 2020 and value 100.

 

[plog]

You need a subquery to determine the latest year for each id:

SELECT Table2.id, Max(Table2.Year) AS LatestYear
FROM Table2
GROUP BY Table2.id;

Paste that into a new query object and save it as 'sub1'. Then to get the results you want use the below SQL:

SELECT Table1.id, sub1.LatestYear, Table2.value
FROM (Table1 LEFT JOIN sub1 ON Table1.id = sub1.id) LEFT JOIN Table2 ON (sub1.LatestYear = Table2.year) AND (sub1.id = Table2.id);

 

[isladogs]

I agree. Thanks for stepping in @plog