Need help with linking a query! (1 Viewer)

[King_kamikaze]

Hello all,

I am having trouble creating a query, here is the query i am having issues with

SELECT a.app_num, Max(b.app) AS Highest, a.prod_code
FROM table1 AS a LEFT JOIN table2 AS b ON a.prod_code = b.GSN
GROUP BY a.app_num, a.prod_code;

Now the query above works fine with out the "a.prod_code" as a column, as you can see i want the maximum value in b.app that relates to a.app_num, what i need to then do is select the b.prod_code the relates to the Max(b.app).

Sorry if this is not clear, please let me know and i will try to elaborate.

Many thanks
Tim

 

[Brianwarnock]

FROM a LEFT JOIN b ON a.prod_num = b.gsn

Brian

 

[King_kamikaze]

Thanks for the response Brian, isnt "FROM a LEFT JOIN b ON a.prod_num = b.gsn" Just the same as what i was already doing though?

"FROM table1 AS a LEFT JOIN table2 AS b ON a.prod_code = b.GSN"

I think the issue may be the method i am using to the select the highest value, is there a different method i could use to prevent the multiple app_num's appearing in the recordset?

 

[King_kamikaze]

Here is a little sample of the query results.

app_num, prod_code, app
00123, N5, 100
00123, L5, 110

what i am trying to achieve is for the query to basically retrieve only the bottom line of data because it has the greater app fee, any suggestions anyone?

Thanks
Tim

 

[Brianwarnock]

Yes King you are correct they are the same, I never code that way and didn't take the time to think .

If I understand correctly your b.gsn a.prod_code provide the link and each prod_num can have multiple a,prod_code.
I think that you must use 2 queries 1st finds max app for each gsn , then second replaces table2 in the original with the resulting query, no grouping etc should be required .

Hope this is clear

Brian

 

[King_kamikaze]

Thanks Brian, Your analysis of my tables is correct and this should give me another approach to try.

Cheers.
Tim

 

[Brianwarnock]

Actually I think that you may need 3 queries
1 select highest app and prod_num
2 select highest app and prod_code
3 link 1 and 2 on highest and select all 3 fields

Brian