CODEWITHSUNDEEP
mysqlsqljoin
user1526780
user1526780

Reputation: 61

Self inner join to get single record

I have an SQL table data as follow enter image description here

I want to display single record for product

example

90792 Amlaan-Hi-Power .............. Show only 1 record when there are 2 record 
90793 Amlaan-Neutral  .............. show only 1 record when there are 2 record 
90794 Amlaan-Phosphate free .........show only 1 record when there are 2 record  
90801 Acetone .......................show only 1 record when there are 2 record 
90901 Acetanilide ...................show only 1 record when there is 1 record

Can I do this using Inner join

I know 

select distinct product from product ORDER BY `product`.`product` DESC

will select distinct (unique) product code and that to only one field i.e. product but confused how to get other information using SQL statement

but results in duplicate records or same table...........................

Upvotes: 0

Views: 63

Answers (2)

krunal nanda
krunal nanda

Reputation: 235

Use group by option for such purposes.

SELECT product,GROUP_CONCAT(product_code SEPERATOR '|') AS product_code,name  FROM Table GROUP BY NAME

It will show only one record for duplicate names.

The multiple enteries of product code will seperated by | .

Upvotes: 0

O. Jones
O. Jones

Reputation: 108641

It looks like your duplicate rows vary by the quantity of product in the package.

You can display just the product and name with

SELECT DISTINCT product, name
  FROM product

If you want to deal with the quantity as well, that's a little trickier. This might work: it will put all product codes on one line.

 SELECT product,
        GROUP_CONCAT(product_code ORDER BY product_code) product_codes,
        name
   FROM product
  GROUP BY product, name

Self join doesn't make a whole lot of sense for this application.

Upvotes: 3

Related Questions