mysql : multiple SELECT statements in Common Table Expression

Question

In need to combine multipe selects into one query.

MariaDB [aix_registry]> SELECT n.name AS WBG from entries e LEFT JOIN nodes n on n.id=e.node_id LEFT JOIN attribs a on a.id=e.attrib_id WHERE  a.name= 'LOCATION' AND e.value='Wienerberg' AND DATE(ts) = CURDATE() LIMIT 5;
+--------------------+
| WBG                |
+--------------------+
| KUG01171_TQLENTW03 |
| AIXSAWBG3          |
| AIXAPPL1EDUC       |
| KUG0114_DDAITAATU  |
| AIXSAPP03C1_HA     |
+--------------------+
5 rows in set (0.001 sec)

MariaDB [aix_registry]> SELECT n.name AS LNZ from entries e LEFT JOIN nodes n on n.id=e.node_id LEFT JOIN attribs a on a.id=e.attrib_id WHERE  a.name= 'LOCATION' AND e.value='Gruberstrasse' AND DATE(ts) = CURDATE() LIMIT 5;
+-------------------+
| LNZ               |
+-------------------+
| ARR5S1P8_OOEGKKPR |
| AIXSAGRU2         |
| AIXSTP11R3DB      |
| STP17T2_SGKKT2    |
| ARR5S1P9_TIC      |
+-------------------+
5 rows in set (0.001 sec)

output should look like this, and the solution should be expandable to any count of query. the two here are just examples.

+-------------------+--------------------+
| LNZ               | WBG                |
+-------------------+--------------------+
| ARR5S1P8_OOEGKKPR | KUG01171_TQLENTW03 | 
| AIXSAGRU2         | AIXSAWBG3          | 
| AIXSTP11R3DB      | AIXAPPL1EDUC       |
| STP17T2_SGKKT2    | KUG0114_DDAITAATU  |
| ARR5S1P9_TIC      | AIXSAPP03C1_HA     |
+-------------------+--------------------+

LIMIT without ORDER BY makes no sense.

Akina
– Akina

2020-06-18 12:25:40 +00:00
Commented Jun 18, 2020 at 12:25 — Akina
– Akina, Commented Jun 18, 2020 at 12:25

Akina · Accepted Answer · 2020-06-18 12:56:46Z

0

WITH
cte AS ( SELECT n.name,
                e.value, 
                ROW_NUMBER() OVER (PARTITION BY e.value 
                                   ORDER BY {expression-1}) AS rn
         from entries e 
         LEFT JOIN nodes n on n.id=e.node_id 
         LEFT JOIN attribs a on a.id=e.attrib_id 
         WHERE  a.name = 'LOCATION' 
           AND e.value IN ('Wienerberg', 'Gruberstrasse')
           AND DATE(ts) = CURRENT_DATE
         ORDER BY {expression-2}
       ),
nums AS ( SELECT 1 rn UNION 
          SELECT 2 UNION 
          SELECT 3 UNION 
          SELECT 4 UNION 
          SELECT 5
        )
SELECT t1.name LNZ, t2.name WBG
FROM nums
LEFT JOIN cte t1 ON nums.rn = t1.rn
LEFT JOIN cte t2 ON nums.rn = t2.rn
WHERE t1.value = 'Gruberstrasse'
  AND t2.value = 'Wienerberg'
--  AND COALESCE(t1.name, t2.name)
ORDER BY nums.rn

edited Jun 18, 2020 at 12:56

answered Jun 18, 2020 at 12:35

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

user11183733 Over a year ago

this produces an error -> #1054 - Unknown column '???' in 'order clause'

Akina Over a year ago

@flynn1973 Have you replaced ORDER BY expressions placeholders with some specific expressions?

user11183733 Over a year ago

is the ORDER BY mandatory? i have no idea what i should order by.

user11183733 Over a year ago

does not work without the order by clauses -> #1052 - Column 'rn' in order clause is ambiguous

Akina Over a year ago

@flynn1973 Imagine taht there is 10 matched rows. You want to receive only 5 of them. If you will not specify orderring you will receive some 5 of 10 rows - and the next quering may return some another 5 rows easily. This is bad. So you must use some unique ordering for to receive the same result on the same data always. It may be, for example, e.id... or e.ts if it is unique... or something else.

|

Collectives™ on Stack Overflow

mysql : multiple SELECT statements in Common Table Expression

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related