CODEWITHSUNDEEP
mysql
tharinduhd
tharinduhd

Reputation: 35

Join two data tables in MYSQL and avoid duplicate values

I created two tables using MYSQL, tblorder and tblitem.

CREATE TABLE `tblorder` (
`orderId` int NOT NULL,
`orderName` varchar(45) NOT NULL,
PRIMARY KEY (`orderId`)

tblorder

CREATE TABLE `tblitem` (
`itemId` int NOT NULL,
`itemName` varchar(45) NOT NULL,
`itemUnit` varchar(5) NOT NULL,
`itemRate` double NOT NULL,
`orderRef` int NOT NULL,
 PRIMARY KEY (`itemId`),
 KEY `fk1_idx` (`orderRef`),
 CONSTRAINT `fk1` FOREIGN KEY (`orderRef`) REFERENCES `tblorder` (`orderId`)

tblitem

I tried to join the two tables using query below

SELECT orderId,orderName, itemName, itemUnit,itemRate
FROM tblitem 
INNER JOIN  tblorder on tblorder.orderId = tblitem.orderRef

Now result show like Image-01

Image-01

How to remove duplicate values in orderId ,OrderName columns in result table? Thanks to help me to solve this problem.

After joining tables is it possible to get output like below? Final output

Upvotes: 1

Views: 196

Answers (1)

tharinduhd
tharinduhd

Reputation: 35

Answer for this question is available in below link. https://dba.stackexchange.com/questions/287746/join-two-tables-in-mysql-and-avoid-duplicate-values

SELECT CASE WHEN sortId = 1 THEN CAST(orderId AS CHAR(10)) ELSE '' END AS 
orderId, CASE WHEN sortId = 1 THEN orderName ELSE '' END AS orderName, 
itemName, itemUnit, itemRate
FROM
(
SELECT orderId, orderName, itemName, itemUnit, itemRate, ROW_NUMBER() OVER 
(PARTITION BY orderId ORDER BY itemId) AS sortId
FROM
(
    SELECT orderId, orderName, itemName, itemUnit, itemRate, itemId
    FROM tblitem 
    INNER JOIN  tblorder 
        on tblorder.orderId = tblitem.orderRef
) orderItems
) orderItemsSorted
ORDER BY orderItemsSorted.orderId, orderItemsSorted.sortId

Upvotes: 1

Related Questions