Mysql – Extract N rows from 2 tables (t1, t2) – if t1 doesn’t match N rows, then (and only then) extract data from t2

MySQLquery

I would like to extract 100 names from table t1 and t2 where id = 'value', in such a way so that if table t1 hasn't 100 names, I will then go and take the remaining names from table t2.

The first part of the query would be (for table t1)

SELECT distinct name, id
FROM t1 WHERE id = 'value' 
ORDER BY id 
LIMIT 100;

Best Answer

You should be able to do this by using a union to join the two queries together. This assumes that Origin is a value that signifies which table is being queried so that results from t1 take precedence.

SELECT 1 as origin, name, id

FROM t1 WHERE id = 'value' 

union

SELECT 2 as origin, name, id

FROM t2 WHERE id = 'value' 

ORDER BY origin, id 

LIMIT 100

Related Solutions

Mysql – Grabbing data from a query with specific criteria, then the data around it

First a query without rank. You may get more than 11 rows in the result, if there people with same number of Kills as your player:

  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills > k.Kills
    ORDER BY a.Kills ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills = k.Kills
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills < k.Kills
    ORDER BY a.Kills DESC
      LIMIT 5
  ) 
ORDER BY Kills DESC ;

This will show no more than 11 rows, even if there hundreds of players with same number of Kills, by applying an order on the two columns: Kills, Name:

  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills, Name  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills = k.Kills AND a.Name > k.Name
      OR a.Kills > k.Kills
    ORDER BY a.Kills ASC, a.Name ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
    WHERE Name = 'VoidWhisperer'
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills, Name  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills = k.Kills AND a.Name < k.Name
      OR a.Kills < k.Kills
    ORDER BY a.Kills DESC, a.Name DESC
      LIMIT 5
  ) 
ORDER BY Kills DESC, Name DESC ;

It will be good if you have a an index on (Kills, Name) and a (unique) index on (Name).

Tested at SQL-Fiddle

Mysql – Order by and then Concat

You can do

SELECT col1, col2,
       CONCAT(LEAST(col1, col2), '-', 
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

Output:

| COL1 | COL2 | CONCAT_RESULT |
|------|------|---------------|
|   A1 |   A2 |         A1-A2 |
|   A2 |   A1 |         A1-A2 |
|   B1 |   A1 |         A1-B1 |
|   A1 |   B1 |         A1-B1 |
|   A1 |   B2 |         A1-B2 |
|   B2 |   A1 |         A1-B2 |
|   A2 |   B1 |         A2-B1 |
|   B1 |   A2 |         A2-B1 |
|   A2 |   B2 |         A2-B2 |
|   B2 |   A2 |         A2-B2 |
|   B1 |   B2 |         B1-B2 |
|   B2 |   B1 |         B1-B2 |

To get distinct values from concatenated result

SELECT DISTINCT 
       CONCAT(LEAST(col1, col2), '-', 
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

Output:

| CONCAT_RESULT |
|---------------|
|         A1-A2 |
|         A1-B1 |
|         A1-B2 |
|         A2-B1 |
|         A2-B2 |
|         B1-B2 |

Here is SQLFiddle demo

Best Answer

Related Solutions

Mysql – Grabbing data from a query with specific criteria, then the data around it

Mysql – Order by and then Concat

Related Question