SELECT n.orgName,n.notificationNumber,Group_CONCAT(p.code,':',p.name SEPARATOR ','), n.notificationType,

n.submissionCloseDateTime, n.publishdate as publishdate,
                                       n.href, n.status, n.placementFeature_name,
                                      n.region, l.subject, l.currencyName, l.maxPrice 
 
                                                    FROM  notification AS n
              
LEFT join lot as l on l.NotificationID = n.id
INNER join products as p on p.NotificationID = n.id     
  Where n.publishdate >=NOW()- INTERVAL 7 day
Group By notificationNumber
    limit 30