HOW to avoid completely showing data which is in another MySQL table?-CodePudding

I am trying to avoid some data which is already in two tables... which means... I used the select statement to get an advertisement which is stored in MySql database using Member Id and I use JOIN tables... there is no problem... all advertisements are showing perfectly which are belong to the member package...

Now the problem is... after the member clicks on an advertisement... it's stored in a table which is called "view" with the date of click, ad_id, and member id. And again the same member should not be able to click that advertisement till the next day... I need to avoid showing that advertisement data using select statements with the condition...

can anyone help me, please? I paste my query below

SELECT `advertisements`.`Ads_ID`,`advertisements`.`AdsName`,`advertisements`.`code`,`advertisements`.`Ad_Value`,`advertisements`.`images`,`advertisements`.`date` FROM `advertisements` JOIN `package_ads` ON `package_ads`.`Ads_ID`=`advertisements`.`Ads_ID` JOIN `packages` ON `packages`.`Package_ID`=`package_ads`.`Package_ID` JOIN `member_package` ON `member_package`.`Package_ID`=`packages`.`Package_ID` JOIN `members2`ON `members2`.`Mem_ID`=`member_package`.`Mem_ID` JOIN `views`ON `views`.`Mem_ID`=`members2`.`Mem_ID` WHERE `member_package`.`Mem_ID`="M100" AND `views`.`clickeddate`!="2021-12-04" AND `views`.`Ads_ID`!=`advertisements`.`Ads_ID`

CodePudding user response：

Whenever you want avoid showing a row based on it's existence in another table you can do that using one of two ways

Use not exists or not in condition
or use a left join and add IS NULL for the joining condition.

The below query will provide you the desired result. It removes from the select query the ads that have already been visited by a particular member on a particular date.

SELECT 
advertisements.Ads_ID,
advertisements.AdsName,
advertisements.code,
advertisements.Ad_Value,
advertisements.images,
advertisements.date 
FROM advertisements 
JOIN package_ads ON package_ads.Ads_ID=advertisements.Ads_ID 
JOIN packages ON packages.Package_ID=package_ads.Package_ID 
JOIN member_package ON member_package.Package_ID=packages.Package_ID 
JOIN members2 ON members2.Mem_ID=member_package.Mem_ID 
LEFT JOIN views ON (views.Mem_ID=members2.Mem_ID and date(views.clickeddate) = current_date and views.Ads_ID=advertisements.Ads_ID)
WHERE 
member_package.Mem_ID="M100" 
AND views.Ads_ID IS NULL