0

I have two tables. One has cId (BigInt ). Other table has comma separated numbers 12,34,53,23,34 (Varchar 255).

news_date is of date type.

I am joining these two table with find_in_set(). I am to join these table.

But max(date) is not working in this case it's picking some random dated row.

SELECT
    `B`.`id` AS `cId`,
    `B`.`ccId` AS `ccId`,
    `A`.`news_date` AS `latestDate`,
    `B`.`status` AS `status`,
    `B`.`isDeleted` AS `isDeleted`
FROM
    `tradefor_mobile_db_tfs_uat`.`tfs_blog` `A`
JOIN `tradefor_mobile_db_tfs_uat`.`tfs_commodity_master` `B`
WHERE

        find_in_set(`B`.`id`, `A`.`cId`) > 0
         AND(`A`.`isDeleted` = 0) AND(`A`.`isActive` = 1) AND(`B`.`isDeleted` = 0) AND(`B`.`status` = 1)        

GROUP BY
    `B`.`id`
HAVING MAX(`A`.`news_date`)

It's giving same date for every id.

This is the result of the above query

sujeet
  • 3,480
  • 3
  • 28
  • 60
  • 1
    Can you change your table design? Having a comma separated list is an awful thing. – juergen d Apr 14 '20 at 06:55
  • @juergend Unfortunately I can't. I know that's the most efficient way out, but I can't. – sujeet Apr 14 '20 at 06:56
  • It's not the join that causes you the problem (beyond what you are already aware of...). See the snswers to the duplicate question on selecting records with a group-wise maximum. – Shadow Apr 14 '20 at 07:30

0 Answers0