0

i have this query which works but only gives the first five rows from the tables. see my former question -> multiple SELECT statements using CTE

WITH
cte AS ( SELECT n.name,
                e.value, 
                ROW_NUMBER() OVER (PARTITION BY e.value 
                                   ORDER BY e.id) 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 e.id
       ),
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 

+----------------------+----------------------+
| LNZ                  | WBG                  |
+----------------------+----------------------+
| AIXVAEBDBT           | KUG01148_JBOSS-T6    |
| OOEGKKT6             | AIXMVBMIGTA2         |
| HSR5S1P8_AM          | KUG01115_WSAP_HA_LPM |
| AIXSTP11R3APP        | AIXTESTHA2C1_HA_LPM  |
| HSR3S1P10_OOEGKKTEST | KUG01142_STP17PR_HA  |
+----------------------+----------------------+

how to i get all the rows without limiting to 5 rows? i skimmed through the mysql cte docs, but this seems to complex for me.

4
  • I see you're using MariaDB.. What version? Commented Jun 22, 2020 at 9:06
  • MariaDB [aix_registry]> SELECT VERSION(); +----------------+ | VERSION() | +----------------+ | 10.4.8-MariaDB | +----------------+ 1 row in set (0.000 sec) Commented Jun 22, 2020 at 9:32
  • Nice.. So how many rows you estimate? Commented Jun 22, 2020 at 9:38
  • the rows are of no fixed count, so just all. i would say these whole num, count voodoo is not even needed. Commented Jun 22, 2020 at 9:46

3 Answers 3

0

You are selecting from nums which will always return 5 rows.

Select from cte and make left join with nums.

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

1 Comment

I will need sample records for all input tables for that.
0

You need 2 CTEs, for the 2 cases of 'Gruberstrasse' and 'Wienerberg' and then apply to them a simulated FULL JOIN (because MySql/MariaDB do not support a FULL JOIN):

WITH
cte1 AS ( SELECT n.name, e.value, 
                 ROW_NUMBER() OVER (PARTITION BY e.value ORDER BY e.id) 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 = 'Gruberstrasse' AND DATE(ts) = CURRENT_DATE
        ),
cte2 AS ( SELECT n.name, e.value, 
                 ROW_NUMBER() OVER (PARTITION BY e.value ORDER BY e.id) 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 = 'Wienerberg' AND DATE(ts) = CURRENT_DATE
        )
SELECT c1.name LNZ, c2.name WBG
FROM cte1 c1 LEFT JOIN cte2 c2
ON c2.rn = c1.rn
UNION
SELECT c1.name LNZ, c2.name WBG
FROM cte2 c2 LEFT JOIN cte1 c1
ON c2.rn = c1.rn

Note that applying the condition:

a.name = 'LOCATION'

in the WHERE clause makes your LEFT join actually an INNER JOIN.
If the code works then fine, but if not then you should move this condition in the ON clause.
The same applies for the condition:

DATE(ts) = CURRENT_DATE

if ts is not a column of the table entries.

5 Comments

#1250 - Table 'c1' from one of the SELECTs cannot be used in ORDER clause
I removed the ORDER BY clause. Is there any specific order? The rows are joined by their row numbers.
hmm..i would say the the conclusion is that this is not doable, your are all very helpfull, but this seems all to overcomplicated and give me serious headache.. thank for alle your input! i will delete this question including all this clutter.
Why do you think it is not doable? The only problem is that MySQL and MariaDB do not support FULL joins. If they did the code would be simpler.
maybe its doable, but as i said this is much to complicated for me. i like thinks stupid simple. i will add a mysql view for each query and use that in my application, not quite state of the art, but at least understandable, this cte stuff is quite arcane
0

Before anything, since you're using MariaDB version above 10.1, you're in luck. MariaDB has such a thing called Storage Sequence Engine built-in. This thing can give you as many numbering sequence you want. So instead of doing:

SELECT 1 rn UNION 
          SELECT 2 UNION 
          SELECT 3 UNION 
          SELECT 4 UNION 
          SELECT 5

You can just do:

SELECT seq AS rn FROM seq_1_to_1000;

This will directly give you numbering sequence from 1 to 1000. Of course, you can add more than that and even using some other function than just running numbers. You can refer to the documentation link I've provided above.

The next thing I assume would be, "what if the rows are not even more than 100?". Like that, you don't need to change the numbering sequence but you can add another HAVING function at the end of the query, and maybe some IFNULL in the SELECT to replace NULL with some value.

Maybe something like this:

WITH
cte AS ( SELECT n.name,
                e.value, 
                ROW_NUMBER() OVER (PARTITION BY e.value 
                                   ORDER BY e.id) 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 e.id
       ),
nums AS ( SELECT seq AS rn FROM seq_1_to_1000 )
SELECT IFNULL(t1.name,0) LNZ, 
       IFNULL(t2.name,0) 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)
HAVING (0) NOT IN ('LNZ','WBG')
ORDER BY nums.rn;

Try this if it would work.

EDIT:

Here's another suggestion

SELECT Seq, 
       IFNULL(GROUP_CONCAT(CASE WHEN B.value='Gruberstrasse' THEN B.Name END),0) AS 'LNZ',
       IFNULL(GROUP_CONCAT(CASE WHEN B.value='Wienerberg' THEN B.Name END),0) AS 'WBG'
FROM seq_1_to_1000 A LEFT JOIN
(SELECT n.name, e.value, 
                ROW_NUMBER() OVER (PARTITION BY e.value 
                                   ORDER BY e.id) 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 e.id) B 
 ON A.seq=B.rn GROUP BY A.seq
 HAVING LNZ+WBG <> 0;

Without using cte:

  1. LEFT JOIN the numbering sequence directly with the sub-query you originally made for the cte.
  2. Using GROUP_CONCAT with CASE expression in SELECT then GROUP BY numbering sequence - added IFNULL to return zero and use that as filter in HAVING.

4 Comments

the rows can be more then 100, so i used the 1 to 1000 sequence, the problem now is it outputs equal numbers for both rows eg LNZ is 31, WBG is 176 but it outputs just 31 for WBG also, even if it is 176.
Right, I think its much simpler to achieve but without any solid data example, I would take a while to do it. Let me try something and get back to you in a bit
In the meantime, how about you remove the HAVING (0) NOT IN ('LNZ','WBG') part and re-run the query, I think that should return all rows. Then probably you can change to HAVING LNZ+WBG <> 0 instead and re-run to see the results.
thank you, very helpfull but as i said below, this whole clutter adds a bunch of complexity and does not seem very practical, especially becaue this two queries are just examples. i have to add about 15 more WHERE clauses to this and it as i see it this cte construct is not expandable to that many queries (multiple wheres and joins).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.