0
Good afternoon, you guys.
https://stackoverflow.com/questions/1925176/sql-server-2008-top-10-and-distinct-together
I need a help, I tried to see in the above section but could not solve my problem.
My problem is the following, I need to make a selection of products and all separated by supplier only, example.
Imagine that in my bank has a register of product and supplier, example, soccer balls, and I have registered 5 balls from Penalty, 19 from Adidas and 3 from Umbro, I wanted to bring a ball from each supplier. 1 Penalty, 1 Adidas, 1 Umbro.
SELECT
distinct top 3 sup.Name as Supplier, pr.CommercialName
FROM Product pr
FULL OUTER JOIN ProductSupplier AS ps ON (pr.IdProduct = ps.IdProduct)
FULL OUTER JOIN Supplier AS sup ON (ps.IdSupplier = sup.IdSupplier)
WHERE
pr.CASNo in ('516849','99879846','68487498','984987','6510016847') or
pr.IdProduct in ('2270')
But instead of bringing only one product from each supplier it brings some random items that are in the middle of the seat.
Altering --
CREATE TABLE Produto (
id int not null primary key identity(1,1),
nome varchar(100),
);
CREATE TABLE Fornecedor (
id int not null primary key identity(1,1),
nome varchar(100)
);
CREATE TABLE produtoFornecedor(
id int not null primary key identity(1,1),
idProduto int FOREIGN KEY REFERENCES produto(id),
idFornecedor int FOREIGN KEY REFERENCES fornecedor(id));
That’s the bank basically my select that I tried was that:
SELECT
distinct sup1.nome as Fornecedor,
(
select
top 1 (pr2.nome)
from Produto pr2
inner JOIN Fornecedor AS sup2 ON (ps.idFornecedor = sup2.idFornecedor)
WHERE sup2.nome = sup1.nome
) as produto
FROM Produto pr
FULL OUTER JOIN Produto AS ps ON (pr.idProduto = ps.idProduto)
FULL OUTER JOIN Fornecedor AS sup1 ON (ps.idFornecedor = sup1.idFornecedor)
This is the mass of data that came back, but it took a null and repeated the products for some reason that I can’t see
To help you better, post the structure of tables and records on http://sqlfiddle.com/
– Tiedt Tech
http://sqlfiddle.com/#! 18/a81ee/8 I didn’t know how you would need me to put in the tables, but basically two normal tables and a table that gets the ids, not being able to insert the ids in the product tableFornecedor why they keep changing whenever I run a script
– user93569
I adjusted your code in sqlfiddle http://sqlfiddle.com/#! 18/d8f92/3. Could you pass me the experienced result with the data you have there:?
– Tiedt Tech
So the idea would be to take one product at a time, based on a supplier, because it cannot repeat the supplier. In the link you sent me would bring from the product tableFornecedor, bring the id 1 and id 3 that will differentiate suppliers, I do not know if I could explain you better
– user93569