CODEWITHSUNDEEP
mysqlsqldatabaseselect
MR_AMDEV
MR_AMDEV

Reputation: 1922

Select from table based on other table with conditions

Advertisements Table

+----+----------+-------------------+
| id | username |    web_address    |
+----+----------+-------------------+
|  1 | admin    | my-best-positions |
|  2 | admin    | after_after       |
|  3 | admin    | after             |
|  4 | admin    | effort            |
+----+----------+-------------------+

activitylogs Table

+----+----------+-------------------+------------+
| id | username |    web_address    | timeperiod |
+----+----------+-------------------+------------+
|  5 | admin    | my-best-positions | 1565021746 |
|  4 | admin    | my-best-positions | 1564935346 |
|  3 | admin    | after_after       | 1564935346 |
|  2 | admin    | after             | 1564935346 |
|  1 | admin    | effort            | 1564935346 |
+----+----------+-------------------+------------+

The timeperiod's from the table above are as follows:

1565021746 = GMT: Monday, August 5, 2019 4:15:46 PM

1564935346 = GMT: Sunday, August 4, 2019 4:15:46 PM

Where is the Fiddle ?

https://www.db-fiddle.com/f/BJJjFHbB1dY7EU1p3auRj/2

Where is the Code ?

SELECT * 
FROM   advertisements 
WHERE  IF(EXISTS (SELECT 1 
                  FROM   activitylogs 
                  WHERE  web_address = advertisements.web_address 
                         AND Timestampdiff(day, From_unixtime(timeperiod), 
                             From_unixtime(Unix_timestamp())) 
                             >= 1 
                         AND Date(From_unixtime(timeperiod)) <> CURRENT_DATE() 
                  ORDER  BY timeperiod DESC) 
           OR NOT EXISTS (SELECT 1 
                          FROM   activitylogs 
                          WHERE  web_address = advertisements.web_address), 
       web_address <> '', 
              NULL)

What is the Current output ?

+----+----------+-------------------+
| id | username |    web_address    |
+----+----------+-------------------+
|  1 | admin    | my-best-positions |
|  2 | admin    | after_after       |
|  3 | admin    | after             |
|  4 | admin    | effort            |
+----+----------+-------------------+

What is the expected Output ?

The output should not contain those rows which have current day's timeperiod but only those which have timeperiod 1 day smaller than current timestamp For example the my-best-positions is getting returned even though it has a timeperiod of today.

Please help as its getting a nightmare to me.

Upvotes: 1

Views: 48

Answers (1)

Strawberry
Strawberry

Reputation: 33935

SELECT a.* 
  FROM advertisements a
  LEFT
  JOIN activitylogs l
    ON l.web_address = a.web_address
   AND DATE(FROM_UNIXTIME(l.timeperiod)) = CURDATE()
 WHERE l.id IS NULL;

Upvotes: 1

Related Questions