I'm pretty new to SQL and I'm working on a query that will save me so much time at work.
I have three tables-
Product- Contains every product (product info table)
Company Offering- Contains every company the product is offered to. (For example, a pen from the product file is listed 10 times on the company offering file because it is available to Company 1, 2, 3, etc.
Companies- Table contains every company ID and company
I need to know which COMPANIES the item is not available to and vice versa (i.e. If someone said start/stop selling this product, I need to know which companies to add/remove it from)
Here is my query:
select s.usn,
s.web_short_description,
s.manufacturer_part_number,
bs.brand_id, bs.source_id,
bs.source_system_id,
bs.brand_SKU_number,
bs.web_enable_date,
bs.web_disable_date,
BR.brand_name
FROM PRODUCT S WITH (NOLOCK)
left join COMPANYOFFERING BS WITH (NOLOCK) on s.usn=bs.USN
join COMPANIES BR WITH (NOLOCK) ON BS.brand_id = BR.Brand_ID
where s.usn is not null
This pulls in all of the data i need, but it's not summarized- each product has several rows- 1 for each company. Ideally, all company names would be listed as a column, and if the item didn't exist for that company, I would get a NULL result.
Thanks in advance for the help!