1

The query for my first table is :

Select * from orders where edited_date<DATE_SUB(now(),interval 48 hour);

The query for my second table is:

Select * from orders where edited_date<DATE_SUB(now(),interval 24 hour);

The second table should display only the filtered records as compared to the first table. I am using mysql.

Shafeeque
  • 2,039
  • 2
  • 13
  • 28
user100613
  • 21
  • 3

4 Answers4

0

You need BETWEEN operator to exclude duplicates in both queries:

Select * from orders where edited_date BETWEEN DATE_SUB(now(),interval 24 hour) AND edited_date<DATE_SUB(now(),interval 48 hour);
Select * from orders where edited_date<DATE_SUB(now(),interval 48 hour);
Michael Sivolobov
  • 12,388
  • 3
  • 43
  • 64
  • Still am not getting wat i want.I just need to find the difference between the two queries that are written in my question.Thanks for the reply – user100613 Oct 22 '13 at 07:22
  • clarify your question. If your first table will output something like this (3, 4, 5) and second: (1, 2, 3, 4, 5). What do you expect as output? My answer outputs next: (3, 4, 5) and (1, 2). P.S. Numbers (1, 2, 3, 4, 5) represents rows in the resultset – Michael Sivolobov Oct 22 '13 at 07:24
  • first table outputs(3,4,5) and second (1,2,3,4,5).i want the difference between the two (1,2) – user100613 Oct 22 '13 at 07:36
  • Did you try my answer? Read my comment? "My answer outputs next: (3, 4, 5) and (1, 2)"... You need (1,2). My answer outputs it. If you want only (1,2) without (3, 4, 5) you can just omit one query. – Michael Sivolobov Oct 22 '13 at 07:46
0

Based on your comments, it seems you have repeated rows in these two queries and you are looking for the set difference. If you got an id column it is quite easy:

; WITH H48 AS (
    SELECT * FROM orders WHERE edited_date<DATE_SUB(now(),interval 48 hour)
), H24 AS (
    SELECT * FROM orders WHERE edited_date<DATE_SUB(now(),interval 24 hour)
)
SELECT * FROM H48 WHERE id NOT IN (SELECT id FROM H24);

Is it what you are looking for?

Gerardo Lima
  • 6,467
  • 3
  • 31
  • 47
0

MySQL doesn’t support the INTERSECT and MINUS set operators. The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets.

The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. We can rewrite these queries by using JOIN operator:

Sample query with the MINUS operator:

 SELECT x, y FROM table_a
 MINUS
 SELECT x, y FROM table_b;

In MySQL:

 SELECT a.x, a.y
 FROM table_a a LEFT JOIN table_b b
 ON a.x = b.x AND a.y = b.y
 WHERE b.x IS NULL;

You can try this logic with your queries. 

 Select a.* 
 from orders a LEFT JOIN orders b
 ON a.column=b.column   ---- add on condition
 where a.edited_date<DATE_SUB(now(),interval 48 hour) and        
       b.edited_date<DATE_SUB(now(),interval 48 hour)

for reference see this post

Sharad
  • 3,562
  • 6
  • 37
  • 59
  • but how will i use this syntax for the above two queries because both my queries already contain where ,and how will i use it again with joins – user100613 Oct 22 '13 at 09:25
  • I have updated the answer but just add ON conditions and see if this could help you. – Sharad Oct 22 '13 at 09:43
0

Try this query

Select orderid from orders 
where 
edited_date<DATE_SUB(now(),interval 24 hour) 
AND 
orderid 
NOT IN(Select orderid from orders where 
edited_date<DATE_SUB(now(),interval 48 hour))

Demo on fiddle

Deepika Janiyani
  • 1,487
  • 9
  • 17