bpajank Posted June 22, 2018 Share Posted June 22, 2018 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! Link to comment Share on other sites More sharing options...
justsomeguy Posted June 23, 2018 Share Posted June 23, 2018 Quote Ideally, all company names would be listed as a column SQL doesn't work that way, each column has a single value. What you can do is do a query that shows you how many companies sell each product, and another query that shows which products are not sold by any company. You can do another one to list every company that sells a particular product. For one of them: SELECT p.usn, p.web_short_description, COUNT(*) AS num_sellers FROM products AS p INNER JOIN companyoffering AS o ON p.usn=o.usn GROUP BY p.usn, p.web_short_description This should list products not sold by anyone: SELECT * FROM products WHERE usn NOT IN (SELECT usn FROM companyofferings) This will do the same thing and may be faster depending on how many records are in the tables: SELECT * FROM products AS p WHERE NOT EXISTS (SELECT * FROM companyofferings AS o WHERE p.usn=o.usn) And then to list companies: SELECT * FROM companies AS c WHERE EXISTS (SELECT * FROM companyofferings AS o WHERE o.brand_id=c.brand_id AND o.usn=123) You'll need to enter the USN you want to figure out which companies sell in that query. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now