I'm trying to select one (1) random row from Table1 where the Data_ID from Table1 does not exist in Table2.
I'm building a site there you can vote on posts (images) and for this page you should only see posts that does not have any votes on it.
Here is a simple structure of my two tables:
CREATE TABLE IF NOT EXISTS `Table1` (
`Id` int(10) NOT NULL AUTO_INCREMENT,
`Data_ID` varchar(10) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `Id` (`Id`,`Data_ID`),
UNIQUE KEY `Data_ID` (`Data_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4389 ;
CREATE TABLE IF NOT EXISTS `Table2` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Data_ID` varchar(10) NOT NULL,
`IP` varchar(20) NOT NULL,
`Vote_ID` int(4) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2314 ;
Thanks!
EDIT:
Here is my try but I don't really get random posts.
SELECT newTable.*
FROM (
SELECT Table1.Id, Table1.Data_ID FROM Table1
LEFT OUTER JOIN Table2
ON Table1.Data_ID = Table2.Data_ID
WHERE Table2.Data_ID IS null
) as newTable
WHERE newTable.Id >= ROUND( RAND() * (SELECT MAX(Id) FROM Table1)) LIMIT 1;
You complicating things too much: subquery with EXISTS
will be enough.
SELECT
Table1.Id,
Table1.Data_ID
FROM
Table1
WHERE
NOT EXISTS(
SELECT * FROM Table2 WHERE Table2.Data_ID = Table1.Data_ID
)
ORDER BY
RAND()
LIMIT
1;
Here is a DESCRIBE
results:
******************** 1. row *********************
id: 1
select_type: PRIMARY
table: Table1
type: index
possible_keys:
key: Data_ID
key_len: 32
ref:
rows: 1
Extra: Using where; Using index; Using temporary; Using filesort
******************** 2. row *********************
id: 2
select_type: DEPENDENT SUBQUERY
table: Table2
type: ALL
possible_keys:
key:
key_len:
ref:
rows: 1
Extra: Using where
UPDv1: There is another fast way, if you like JOIN
fashion:
SELECT
Table1.Id,
Table1.Data_ID
FROM
Table1 LEFT OUTER JOIN Table2 USING(Data_ID)
WHERE
Table2.Id IS NULL
ORDER BY
RAND()
LIMIT
1;
DESCRIBE
result:
******************** 1. row *********************
id: 1
select_type: SIMPLE
table: Table1
type: index
possible_keys:
key: Data_ID
key_len: 32
ref:
rows: 1
Extra: Using index; Using temporary; Using filesort
******************** 2. row *********************
id: 1
select_type: SIMPLE
table: Table2
type: ALL
possible_keys:
key:
key_len:
ref:
rows: 1
Extra: Using where; Not exists; Using join buffer (Block Nested Loop)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句