MySQL – Join Two Tables and Avoid Duplicate Values

MySQL

I created two tables using MYSQL, tblorder and tblitem.

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

enter image description here

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`)

enter image description here

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

enter image description here

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?

enter image description here

Output from new Query

enter image description here

Best Answer

Agreed with the other comments about handling this in the application side, as what you're looking for is more so presentation logic but this should accomplish it from the database side too:

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

This works by generating a unique ID (with the ROW_NUMBER() window function) within each group of rows per orderId, ascending in the order of the itemId (which is what your requested results show), which I call the sortId. Then with that sortId you can check if the current row is the first item for that order, and then display the full row details, otherwise anything after the first item per order will blank out the orderId and orderName.

By the way, I normally prefer to put each subquery into its own CTE instead for readability, but not sure which version of MySQL you're on and if it supports CTEs, so subqueries it is for now.

Related Question