asked
How to do a multiple record search on multiple codes corresponding to a single row result?
![alt text][1] I have these two tables, and my question is what is the SQL query to produce the following result? ![alt text][2] [1]: /storage/temp/3095-tables.png [2]: /storage/temp/3096-desired-query-results.png In my system, a keycode equals to N1 is understood to be a location value, while a keycode that is G3 is a main product. I want to get the latest updates, and the End date field serves that purpose.
-- i think this should work if endate is 12/31/2099 or else use same logic to --find the max date per keycode and supplierid SELECT SUPNAME, location,food from SUPPLIER S join (select supid, keyvalue as location from codes where enddate='12/31/2099' and keycode='N1') LOC ON S.SUPID=LOC.supid join (select supid, keyvalue as FOOD from codes where enddate='12/31/2099' and keycode='G3') FD on s.SUPID = FD.supid
18 People are following this question.
