I’m use an SQL to Excel program to create a report for products in a store which have not been ordered in 6 months or more. Most of the data is in a single table so it works fine as below:

select 
    stockmst.pm_part as PLU, 
    stockmst.pm_actind as ACTIVE,
    stockmst.pm_anal3 as BRAND, 
    stockmst.pm_desc as DESCRIPTION, 
    stockmst.supppurchunit as SIZE, 
    stockmst.pm_retail as PRICE, 
    stockmst.pm_prefsup as SUPPLIER 
    
    from stockmst 

where stockmst.pm_part not in 
    (select itemcode from grnline where grnno in 

    (select grnno from grnhead where dttimestamp > getdate()-180))

However, there is certain data I need to pull from other tables.

stockmst.pm_prefsup gives me the abbreviated code for the supplier, but not the description. There are many suppliers on file, so a code isn’t always descriptive enough to work with.

I need to add the following:

su_name as Supp1  
    su_sname as Supp2
    
    
    from SUPPLIER

And have those values appear as additional columns to the above.

Could I get some guidance on this please?

I tried duplicating the SELECT which didn’t work, and couldn’t figure out how to use JOIN (I’m very new to this)