I recently needed to select all assigned purchasing accounts for a particular vendor; this would include both the normal purchasing one on the Vendor Account Maintenance window and the additional ones in the Additional Vendor Accounts window.
The SQL is fairly straightforward, but I had to make sure each account was only included once:
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT DISTINCT
['Account Master'].ACTINDX
,['Account Index Master'].ACTNUMST
,['Account Master'].ACTDESCR
FROM
PM00200 AS ['PM Vendor Master']
LEFT JOIN
PM00203 AS ['Vendor Accounts']
ON
['Vendor Accounts'].VENDORID = ['PM Vendor Master'].VENDORID
LEFT JOIN
GL00100 AS ['Account Master']
ON
['Account Master'].ACTINDX = ['Vendor Accounts'].ACTINDX
OR
['Account Master'].ACTINDX = ['PM Vendor Master'].PMPRCHIX
LEFT JOIN
GL00105 AS ['Account Index Master']
ON
['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
WHERE
['PM Vendor Master'].VENDORID = 'ADVANCED0001'
GO