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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…