Select ONE random row from Table1 where id does not exist in Table2

Tobias Lindberg

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;
BlitZ

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] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

SQL table1 row_id不显示table2 row_id

来自分类Dev

mysql union select where table1 field3 = table2 field5

来自分类Dev

MySQL SELECT * FROM table1,table2,table3

来自分类Dev

如何从“ TABLE1 AND TABLE2”创建SELECT查询

来自分类Dev

使用批处理插入时如何识别错误行(INSERT INTO table2 SELECT * FROM table1)

来自分类Dev

使用批处理插入时如何识别错误行(INSERT INTO table2 SELECT * FROM table1)

来自分类Dev

如果 table1 中的 table2 id 不为空,如何加入 table2

来自分类Dev

显示来自table1的数据,其中其id(Table1 Id)未包含在table2中

来自分类Dev

select * from table where id = select max id in codeigniter

来自分类Dev

SELECT * FROM table_1 WHERE ID =表_2中的ID

来自分类Dev

SELECT * FROM table_1 WHERE ID =表_2中的ID

来自分类Dev

table1 在 table1.varchar 上连接 table2 等于 prefix+table2.numeric_id+suffix

来自分类Dev

SQL Server matching all rows from Table1 with all rows from Table2

来自分类Dev

“With”命令比使用临时表慢 Select * into #table1 from

来自分类Dev

使用where子句从table1中选择不在table2中的特定数据

来自分类Dev

使用where子句从table1中选择不在table2中的特定数据

来自分类Dev

PHP(“ SELECT * FROM Table WHERE ... AND ...”)

来自分类Dev

SQL Server-将Table1加入Table2 ON子串(T1.Field)= T2.ID

来自分类Dev

SQL - 从 table2 更新 table1 值

来自分类Dev

选择 table1 和 table2 的计数

来自分类Dev

从table1以及mysql中的table2中选择所有结果,其中两个表都有一个公共ID

来自分类Dev

MySQL内部联接,其中table2 child_id类似于table2 id

来自分类Dev

MySQL SELECT FROM 3 Table with where

来自分类Dev

没有这样的表:TABLE_TEAM(代码 1):,编译时:SELECT * FROM TABLE_TEAM WHERE (TEAM_ID = '133604')

来自分类Dev

与SELECT col1 FROM table WHERE col2 = somestring对应的Django ORM是什么?

来自分类Dev

相当于SELECT * FROM table WHERE column1 = column2的熊猫

来自分类Dev

mysql select * from table group by id with rollup

来自分类Dev

Most efficient way to SELECT rows WHERE the ID EXISTS IN a second table

来自分类Dev

如果整行不存在于 table2 中,则插入 table2 从 table1 中选择

Related 相关文章

  1. 1

    SQL table1 row_id不显示table2 row_id

  2. 2

    mysql union select where table1 field3 = table2 field5

  3. 3

    MySQL SELECT * FROM table1,table2,table3

  4. 4

    如何从“ TABLE1 AND TABLE2”创建SELECT查询

  5. 5

    使用批处理插入时如何识别错误行(INSERT INTO table2 SELECT * FROM table1)

  6. 6

    使用批处理插入时如何识别错误行(INSERT INTO table2 SELECT * FROM table1)

  7. 7

    如果 table1 中的 table2 id 不为空,如何加入 table2

  8. 8

    显示来自table1的数据,其中其id(Table1 Id)未包含在table2中

  9. 9

    select * from table where id = select max id in codeigniter

  10. 10

    SELECT * FROM table_1 WHERE ID =表_2中的ID

  11. 11

    SELECT * FROM table_1 WHERE ID =表_2中的ID

  12. 12

    table1 在 table1.varchar 上连接 table2 等于 prefix+table2.numeric_id+suffix

  13. 13

    SQL Server matching all rows from Table1 with all rows from Table2

  14. 14

    “With”命令比使用临时表慢 Select * into #table1 from

  15. 15

    使用where子句从table1中选择不在table2中的特定数据

  16. 16

    使用where子句从table1中选择不在table2中的特定数据

  17. 17

    PHP(“ SELECT * FROM Table WHERE ... AND ...”)

  18. 18

    SQL Server-将Table1加入Table2 ON子串(T1.Field)= T2.ID

  19. 19

    SQL - 从 table2 更新 table1 值

  20. 20

    选择 table1 和 table2 的计数

  21. 21

    从table1以及mysql中的table2中选择所有结果,其中两个表都有一个公共ID

  22. 22

    MySQL内部联接,其中table2 child_id类似于table2 id

  23. 23

    MySQL SELECT FROM 3 Table with where

  24. 24

    没有这样的表:TABLE_TEAM(代码 1):,编译时:SELECT * FROM TABLE_TEAM WHERE (TEAM_ID = '133604')

  25. 25

    与SELECT col1 FROM table WHERE col2 = somestring对应的Django ORM是什么?

  26. 26

    相当于SELECT * FROM table WHERE column1 = column2的熊猫

  27. 27

    mysql select * from table group by id with rollup

  28. 28

    Most efficient way to SELECT rows WHERE the ID EXISTS IN a second table

  29. 29

    如果整行不存在于 table2 中,则插入 table2 从 table1 中选择

热门标签

归档