CODEWITHSUNDEEP
sqlsql-server
pabaras
pabaras

Reputation: 51

SQL Server 2012 Query

I have two tables. The one is products and the other is suppliers. The table products has product_id, product_price, product_name, supplier_id and category_id columns. The supplier_id is the foreign key from suppliers table and the product_id is the primary key. The suppliers table has supplier_id, supplier_name, state and city columns. The query that I want to make is "select full details of the supplier with the largest number of products." I was thinking something like this:

SELECT * FROM suppliers INNER JOIN products ON suppliers.supplier_id=products.supplier_id;

but I dont know how to count every supplier's products. Any ideas? Thanks is advance.

Upvotes: 0

Views: 51

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

Don't join. You want data from the suppliers table where a certain condition (largest number of products) is given. Hence suppliers in the FROM clause, products in the WHERE clause.

You'd group the products table by supplier_id, so as to get an aggregate per supplier. Order by count and take the top record(s).

select * 
from suppliers
where supplier_id in
(
  select top(1) with ties
    supplier_id
  from products
  group by supplier_id
  order by count(*) desc
);

Upvotes: 1

Related Questions