CODEWITHSUNDEEP
mysql
dnc123
dnc123

Reputation: 141

SQL - getting the rows that aren't there

Title might sound strange but I'll try to explain the problem as easy as possible. Let's start with example. I got a table with 2 columns - id, ip. Let's say I got 3 rows with id 2,3,5. Now, I need to get any row that isn't between id 1 AND 5, which is clearly 1 and 4. Currently I'm stuck with this query:

SELECT * 
FROM `votes` 
WHERE ip = "1.1.1.1."  
AND question_id BETWEEN 1 AND 5

Upvotes: 3

Views: 61

Answers (3)

dnc123
dnc123

Reputation: 141

Ok, I find the solution -

I made new column for each question_id and all ip fields I filled with NULL, after that I use this logic in SQL query: 

SELECT question_id
FROM  `votes` 
WHERE (ip =  'NULL' OR ip = ?)
GROUP BY question_id
HAVING COUNT(*) - COUNT(DISTINCT question_id) = 0
ORDER BY RAND()
LIMIT 1

And in result I get one random row that haven't been used yet, because it searches for duplicates, and if any duplicates are found they are removed from search. I hope I wrote this understandable

Upvotes: 1

Drew
Drew

Reputation: 24959

sounds bizarre but this is what many do.

create a helper table. Use it for left joins

create table amfn
(   -- All My Favorite Numbers  
    id int auto_increment primary key,
    theWhat char(1) null
)engine=MyIsam;   --  <----- somewhat important

insert amfn(theWhat) values (null),(null),(null),(null),(null),(null),(null),(null),(null),(null); -- 10
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;

select count(*),min(id),max(id) from amfn;
+----------+---------+---------+
| count(*) | min(id) | max(id) |
+----------+---------+---------+
|  1310720 |       1 | 1310720 |
+----------+---------+---------+
1 row in set (0.00 sec)

Your Schema:

create table votes
(   question_id int not null,
    ip varchar(20) not null
);
insert votes (question_id,ip) values (1,'xxxx'),(2,'1.1.1.1'),(3,'1.1.1.1'),(4,'1.6.1.1'),(5,'1.1.1.1');

The Query:

select a.id,v.question_id,v.ip
from amfn a
left join votes v
on v.question_id=a.id and v.ip='1.1.1.1'
where a.id between 1 and 5 and v.question_id is null;
+----+-------------+------+
| id | question_id | ip   |
+----+-------------+------+
|  1 |        NULL | NULL |
|  4 |        NULL | NULL |
+----+-------------+------+
2 rows in set (0.00 sec)     <------------- boy that is fast

Edit (to show Conrad Frix the time differences).

My above approach to create 5242880 rows, 23.5 seconds. Conrad's approach, 168.5 seconds. I will stick with my approach :>

Upvotes: 2

Alex Shesterov
Alex Shesterov

Reputation: 27525

A really unusual request;

But you may get what you want by creating an auxiliary sequence of 1..5, then select all the ids from this sequence where there's no matching entry in the votes table. 

SELECT id 
FROM (
  SELECT 1 AS id UNION ALL 
  SELECT 2 UNION ALL 
  SELECT 3 UNION ALL 
  SELECT 4 UNION ALL 
  SELECT 5 UNION ALL 
) AS temp 
WHERE temp.id NOT IN (SELECT DISTINCT question_id FROM votes);

Upvotes: 1

Related Questions