Geek asks and answers

Returns different data from mysql based on the number of rows returned from the subquery

I have a table of records that are either orphaned or hit in pairs. Each item has a status associated with it. I need to select a random orphan or pair entry from the table (both entries, if its a pair), but NOT couples that have a status equal to 0 for one.

I have the following table:

    Table: things
+------------+---------++---------+
| id         | cluster | status   |
+------------+---------++---------+
| 1          |  1      | 0        |
| 2          |  1      | 1        |
| 3          |  3      | 1        |
| 4          |  4      | 1        |
| 5          |  4      | 0        |
| 6          |  6      | 1        |
| 7          |  6      | 1        |
| 8          |  8      | 1        |
+------------+---------++---------+

My request

SELECT 
things.id as clusterid, 
things.id as id1, 
things2.id as id2,   
(SELECT count(id) FROM things WHERE cluster= clusterid) as num_nodes
FROM things
LEFT JOIN things AS things2 ON (
    things2.status= 1  
    AND things2.cluster= things.cluster
    AND things2.id!= things.cluster)
WHERE things.status= 1 
AND things.id= things.cluster
ORDER BY RAND() LIMIT 1

This query should return any of the following combinations of records in random order (enumeration of identifiers):

3
6 and 7
8

My query does this, but it also returns id 4, which should not be here, since num_nodes will return 2, but since the status of the 2nd element in the cluster is 0 ( id 5), this pair should be ignored.

, num_nodes mysql.

, 2 , . , status = 1, 1 . , status = 1, 2 .

+3
2

LEFT JOIN, 0, , 1, :

SELECT 
  things.id  AS clusterid, 
  things.id  AS id1, 
  things2.id AS id2,   
  (SELECT count(id) FROM things WHERE cluster = clusterid) AS num_nodes
    -- num_nodes could instead be defined as: IF(things2.id IS NULL, 1, 2)
FROM
            things
  LEFT JOIN things AS things2 ON (
        things2.cluster = things.cluster
    AND things2.id     != things.cluster
  )
WHERE
      things.status = 1
  AND (things2.id IS NULL OR things2.status = 1)
  AND things.id = things.cluster
ORDER BY RAND()
LIMIT 1

:

SELECT * FROM things AS t WHERE cluster IN (
  SELECT things.cluster 
    FROM
                things
      LEFT JOIN things AS things2 ON (
            things2.cluster = things.cluster
        AND things2.id     != things.cluster
      )
    WHERE
          things.status = 1
      AND (things2.id IS NULL OR things2.status = 1)
      AND things.id = things.cluster
    ORDER BY RAND()
    LIMIT 1
)
0

LIMIT . eggyal.

  SELECT * FROM things AS t WHERE cluster IN (
    SELECT * FROM (
      SELECT things.cluster 
        FROM
                    things
          LEFT JOIN things AS things2 ON (
                things2.cluster = things.cluster
            AND things2.id     != things.cluster
          )
        WHERE
              things.status = 1
          AND (things2.id IS NULL OR things2.status = 1)
          AND things.id = things.cluster
        ORDER BY RAND()
        LIMIT 1
    ) as cheese
  )
+1

More articles:


All Articles