I’m using SQL in an 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 certain data I need to pull from other tables.
stockmst.pm_prefsup returns 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)
Gwame is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Typically when you are trying to query across multiple tables, you must connect them in some way, by some column in each that stores the same information. For instance, one table might have a person’s phone number and their name, and another table might have their name and their email. To get all of the information associated with a person, you might do something like this:
SELECT
email_table.name, email_table.email, phone_table.phone
FROM
email_table, phone_table
WHERE
email_table.name == phone_table.name
Here is a link that explains how to do stuff like this with SELECT, JOIN, etc. – this seems to be what you need:
In your case, you might select from both the supplier table and the stockmst table, where stockmst.pm_prefsup == supplier.su_name, assuming that the format of supplier.su_name is the same as stockmst.pm_prefsup.
Assuming the supplier abbreviation is in a field named su_abbrev your query would be:
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,
su_name as Supp1,
su_sname as Supp2
FROM stockmst
INNER JOIN SUPPLIER ON stockmst.pm_prefsup = SUPPLIER.su_abbrev
where stockmst.pm_part not in
(select itemcode from grnline where grnno in
(select grnno from grnhead where dttimestamp > getdate()-180))
fiddle