1

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

2
  • This is not possible – JYOTI RAJAI Mar 26 at 7:22
  • @JYOTIRAJAI it is possible, but this is not how the database is supposed to work – Kondybas Mar 26 at 7:43
1

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.

9
  • @ J.D. Tks for your valuable answer for the question. I attempted using that. It is rather successful but not gives as expected result. Anyway I am working on that further. – tharinduhd Mar 26 at 15:43
  • @tharinduhd Could you provide an example of an unexpected result using it and what the expected result should be? I don't have a good way to test without your data, but I believe the above should provide at least what your last screenshot shows. I can try to fix whatever is missing in my query. – J.D. Mar 26 at 16:33
  • @ J.D. I mention in my above comment "as expected result" is last screen shot view in my question. As sample data still I am using data in tblorder and tblitem. I used the query written in your answer. But it gives result in different way but not according to – tharinduhd Mar 26 at 16:57
  • the view in question. – tharinduhd Mar 26 at 17:08
  • 1
    @ J.D. Thanks a lot for your great support to solve my problem. It works perfectly. I missed something in your query earlier. sorry for that and my late reply. – tharinduhd Mar 27 at 12:08
1

Do this "deduplication" on the application side. It will be much easier to clean up repetitive values with PHP or JS. It is possible to make what you want by UDVs but SQL wasn't intended for it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.