SQL Selecting data from 3 tables using GROUP BY

Question

I am trying to write a formula to pull data from 3 tables and struggling to get it working.

I need to use the InventoryNbr from table s and do a group by, because there is many of the same InventoryNbr's, and I just want the MAX IndexListID Returned. The IndexListID is a Unique Key, so when I search on table il, I should only return 1 row. I want to then pull the end year from that row as well as the modelnm, and use those 2 values to get the CarlineNm. Here is my code:

SELECT s.InventoryNbr, MAX(s.IndexListID) AS IndexListID, il.EndYear, c.CarlineNm
FROM sysidla as s
INNER JOIN IndexList as il
ON s.IndexListID = il.IndexListID
INNER JOIN Carline as c
ON il.EndYear = c.CarlineYear
AND il.ModelNm = c.ModelNm
GROUP BY InventoryNbr
ORDER BY InventoryNbr ASC;

The error I keep getting is:

Column 'IndexList.EndYear' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Here is some sample data:

sysidla
InventoryNbr|IndexListID|Junk|Junk2
12345|1|x|y
12345|2|c|r
12345|3|c|e
12346|4|e|w

IndexList
ModelNm|Junk|Junk1|Junk3|EndYear|IndexListID
name1|c|f|r|2004|1
name2|c|f|r|2008|2
name3|c|f|r|2012|3
name4|c|f|r|2004|4
name5|c|f|r|2018|5

Carline
CarlineYear|CarlineNm|Junk9|ModelNm
2005|NAME|d|name1
2012|NAME22|d|name3
2005|NAME354|d|name4
2005|NAME1|d|name5

So for instance, this is an incomplete data sample because every IndexListID will have a match in IndexList, but I want to be able match InventoryNbr 12345 and select the max INDEXLISTID which is 3, then use 3 on IndexList to grab name3 and 2012. Then I want to use 2012 and name3 to get NAME22 From Carine.

question from:https://stackoverflow.com/questions/65863478/sql-selecting-data-from-3-tables-using-group-by

Answer 1

Your are getting this error because, there is a problem with your group by and SELECT section . You can not select column while using group by like this. Either you have to use an aggregate function or your column should in the group by to select like this.
So,Here is a solution you can check =>

SELECT T.*,il.EndYear, c.CarlineNm FROM 
(SELECT s.InventoryNbr, MAX(s.IndexListID) AS IndexListID
FROM sysidla as s
GROUP BY InventoryNbr) T
INNER JOIN IndexList as il ON T.IndexListID = il.IndexListID
INNER JOIN Carline as c ON il.EndYear = c.CarlineYear AND il.ModelNm = c.ModelNm
ORDER BY T.InventoryNbr ASC;

Note: This code is not optimized. Sub-query is little slow however, you can optimize that using window function (CTE).
Please check and let me know.