CODEWITHSUNDEEP
mysql
SamSebastien
SamSebastien

Reputation: 181

MySQL query with WHERE clause, but drop a condition if no results?

I'm currently doing this via two separate queries from PHP, but would love to optimize and somehow in a single query.

First query..

SELECT `referrer` 
FROM `tbl_traffic_log` 
WHERE `domain` = 'mysite.com' 
  AND `referrer` != '$referringDomain' 
  AND CASE WHEN `clicks_in_unique`=0 THEN 2 ELSE `clicks_out_unique`/`clicks_in_unique` END < 1.4 
ORDER BY RAND() 
LIMIT 1

..and if mysql_num_rows shows no results, I do a second query to try again and check if there are any results minus the referrer != 'partner1.com' part.

The code is basically trying to find a random trade partner who ISN'T the partner who sent that click, but if there are no matches, as a last resort it's ok to send back, provided it matches the other criteria.

I'm pretty sure there is a way to do this, but just can't find a way after searching (probably because I'm not understanding the problem enough to type in the right thing).

Any other critique of the query is welcome as well.

Thank you :)

Upvotes: 0

Views: 210

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I think you can do this like this:

SELECT `referrer` 
FROM `tbl_traffic_log` 
WHERE `domain` = 'mysite.com' 
  AND CASE WHEN `clicks_in_unique`=0 THEN 2 ELSE `clicks_out_unique`/`clicks_in_unique` END < 1.4 
ORDER BY `referrer` != '$referringDomain' desc, RAND() 
LIMIT 1

The idea is to put the condition in the order by. The condition (in MySQL) evaluates to either 0 or 1, so we want where the condition is true first (hence the desc). It then chooses a random row. If there are no rows where the condition is true, then it chooses a random row.

Upvotes: 1

Related Questions