如果在SELECT Query中首先匹配，如何跳过其他OR条件？

Question

I am having a trouble with OR condition inside the SELECT.

I want a simple result if one condition is matched and rest OR condition should not be use.

What i want is:

I have some users shared records and i would like to email them the newest items shared on my website.

For me: Newest Items will be least two days older

Like Today is 9th so i would like to pull all records of 7th. but if i didn't get any record of 7th then i would like to pull all record of 6th (3 days older from today). if i didn't get any records on 6th then i would like to pull 1 day older from today.

for all this i have used OR in my SELECT query like this:

SELECT `tg`.* FROM `tblgallery` AS `tg` WHERE (
(tg.added_date BETWEEN '2014-07-07 00:00:00' AND '2014-07-08 00:00:00') OR 
(tg.added_date BETWEEN '2014-07-06 00:00:00' AND '2014-07-07 00:00:00') OR 
(tg.added_date BETWEEN '2014-07-08 00:00:00' AND '2014-07-09 00:00:00') )

And i have records in my database for dates:

2014-07-06
2014-07-07

and when i run this query it gives me all record of both dates.

But I need to pull only record of 2014-07-07 not of both.(I have mentioned above.)

I know i can do this by using multiple Select and i think that will not be a good idea to request to database again and again.

My Question is : How to pull data from database if the first match is true? and skip all data of rest dates?

OR

Is there any other way to do this?

Please Help

Answer 1

Usually one would just work with LIMIT, which is not applicable here, since there might be many rows per day. What I do is quite similar to LIMIT.

SELECT * FROM (
   SELECT
   tg.*,
   @gn := IF(DATE(tg.added_date) != @prev_date, @gn + 1, @gn) AS my_group_number,
   @prev_date := DATE(tg.added_date)
   FROM tblgallery tg
   , (SELECT @gn := 0, @prev_date := CURDATE()) var_init
   ORDER BY FIELD(DATE(tg.added_date), CURDATE() - INTERVAL 1 DAY, CURDATE() - INTERVAL 3 DAY, CURDATE() - INTERVAL 2 DAY) DESC
) sq
WHERE my_group_number = 1;

Here's how it works.

With this line

   , (SELECT @gn := 0, @prev_date := CURDATE()) var_init

the variables are initialized.

Then the ORDER BY is important! The FIELD() function sorts the rows from 2 days ago (gets value 3), to 3 days ago (gets value 2), to 1 day ago (gets value 1). Everything else gets value 0.

Then in the SELECT clause the order is also important.

With this line

   @gn := IF(DATE(tg.added_date) != @prev_date, @gn + 1, @gn) AS my_group_number,

the variable @gn is incremented when the date of the current row is different from the date of the previous row.

With this line

   @prev_date := DATE(tg.added_date)

the date of the current row is assigned to the variable @prev_date. In the line above it still has the value of the previous row.

Now those entries have a 1 in column my_group_number that have the most recent date in the order

2 days ago
3 days ago
yesterday
4 days ago
5 days ago
...