CODEWITHSUNDEEP
sql
user543425
user543425

Reputation: 1

Left Join returns more records

I have 2 tables with related data. one table is for products. and the other price. In price table one product may appear several times. How can I return the result showing the products without containing duplicate rows

My Query is 

select             p.Product, sum(p.Qty), max(pr.netprice)
from               Products  p
  left outer join  Price pr
  on               p.Product=pr.Product
where              p.brand=''
group by           p.Product,pr.Product

but return more rows as right table have multiple records

please help

Upvotes: 0

Views: 1220

Answers (6)

Peposh
Peposh

Reputation: 182

What about changing it this way:

SELECT p.Product, sum(p.Qty), 
       (SELECT max(pr.netprice) 
          FROM Price pr 
         WHERE p.Product=pr.Product
       )
  FROM  Products  p
 WHERE  p.brand=''

Upvotes: 0

jose alsina
jose alsina

Reputation: 1

If you want the sum then use Tomas answer above. This will give you a unique product list with the total quantity and maximum price for each product

select p.Product, sum(p.Qty), max(pr.netprice) 
from Products p 
left outer join Price pr on p.Product = pr.Product 
where p.brand = '' 
group by p.Product

Upvotes: 0

Bryant Bowman
Bryant Bowman

Reputation: 423

Use the distinct keyword. That will remove duplicates. ALthough, if there are different prices for a given product, there will be one record per unique price per product if you remove the Max().

select DISTINCT p.Product, sum(p.Qty),max(pr.netprice)
from Products p 
left outer join Price pr on p.Product=pr.Product 
where p.brand='' group by p.Product,pr.Product

Upvotes: 1

Tomas Jansson
Tomas Jansson

Reputation: 23472

I don't think distinct is the way to go, I think group by should result in what you want if done correctly. Also, I don't think you need to group on values from both tables.. You should really understand what you want to. Give us example data and it will be easier to answer your question. Try this:

select p.Product, sum(p.Qty), max(pr.netprice)
from Products p
left outer join Price pr on p.Product = pr.Product
where p.brand = ''
group by p.Product -- only group on param.

Upvotes: 2

jose alsina
jose alsina

Reputation: 1

Try this

SELECT p.Product, p.Qty, MAX(pr.netprice) FROM Products p LEFT OUTER JOIN Price pr ON p.Product=pr.Product WHERE p.brand='' GROUP BY p.Product, p.Qty

Upvotes: -1

Paul
Paul

Reputation: 314

Try putting distinct in select. I am not sure it will work, but try it.

Upvotes: 0

Related Questions