Combining Two Queries Without the Use of Certain Functions

Question

I have two separate queries:

SELECT TIME_TO_SEC(SUM(odchodDoch - prichodDoch)) AS cisloDoch,
  SUM(prestavkaDoch) as presDoch,
  operatorDoch 
  FROM dochazkaVyroba 
    WHERE operatorDoch IN ("x","y","z")
    AND datumDoch BETWEEN "2017-06-01" AND "2017-06-02"
    GROUP BY operatorDoch;

SELECT SUM(CAS) as cisloVyk,
  JMENO 
  FROM produkce 
    WHERE JMENO IN ("x","y","z")
    AND DATUM BETWEEN "2017-06-01" AND "2017-06-02"
    GROUP BY JMENO;

And I wish to combine these into a single query.

and separately I get the following results:

cisloDoch presDoch operatorDoch
 57600      60         x
  0         0          y
 57600      0          z


cisloVyk JMENO 
 532       x
  0        y
 780       z

What I am looking to do is combine the above queries into one, based on:

produkce.JMENO = dochazkaVyroba.operatorDoch,

so that I get following:

cisloDoch presDoch operatorDoch  cisloVyk JMENO 
 57600      60         x             532    x
  0         0          y               0    y
 57600      0          z              780   z

Unfortunately, FULL OUTER JOIN isn't usable in MySQL.

I also cannot use UNION with ON produkce.JMENO = dochazkaVyroba.operatorDoch.

There is a way to emulate full outer join. Take a look here, maybe this will help you: https://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — heliosk, Jun 06 '17 at 18:28

score 0 · Answer 1 · answered Jun 06 '17 at 18:19

0

The brute force way would be to just join the two subqueries:

SELECT
    t1.cisloDoch,
    t1.presDoch,
    t1.operatorDoch,
    t2.cisloVyk,
    t2.JMENO
FROM
(
    SELECT
        TIME_TO_SEC(SUM(odchodDoch - prichodDoch)) AS cisloDoch, 
        SUM(prestavkaDoch) AS presDoch,
        operatorDoch 
    FROM dochazkaVyroba 
    WHERE operatorDoch IN ("x","y","z") AND
          datumDoch BETWEEN "2017-06-01" AND "2017-06-02"
    GROUP BY operatorDoch
) t1
INNER JOIN
(
    SELECT SUM(CAS) as cisloVyk, JMENO 
    FROM produkce 
    WHERE JMENO IN ("x","y","z") AND
          DATUM BETWEEN "2017-06-01" AND "2017-06-02"
    GROUP BY JMENO
) t2
    ON t1.operatorDoch = t2.JMENO

answered Jun 06 '17 at 18:19

Tim Biegeleisen

502,043
27
286
360

Thank you for answer. I need OUTER JOIN but mySQL doesn't know it. Just out of curiosity I have tried you code and got errors. – Armen Nazaryan Jun 06 '17 at 18:37
9 errors were found during analysis. An expression was expected. (near "(" at position 183) Unexpected token. (near "(" at position 183) Unexpected token. (near "(" at position 184) This type of clause was previously parsed. (near "SELECT" at position 185) Unexpected token. (near ")" at position 582) Unexpected token. (near ")" at position 857) Unexpected token. (near "op" at position 859) This type of clause was previously parsed. (near "GROUP BY" at position 863) Unrecognized statement type. (near "GROUP BY" at position 863) – Armen Nazaryan Jun 06 '17 at 18:37
Does each individual query run by itself? I'm afraid that without seeing your data and setup, I can't help much here. – Tim Biegeleisen Jun 07 '17 at 00:23
Yes individualy it runs by itself just fine. When I use UNION it also works but when I specify ON produkce.JMENO = dochazkaVyroba.operatorDoch It throws errors. MySQLi 5.7.18 – Armen Nazaryan Jun 07 '17 at 08:40
`ON produkce.JMENO` ... this isn't from my query. Did you run my _exact_ query, or did you take it and modify it? – Tim Biegeleisen Jun 07 '17 at 08:43
I tried to run your without modification I was talking when I tried union and btw when I used union without on and without SELECT FROM and the rest of code it worked but when I true to use with union SELECT* FROM sq1 UNION sql2 it also throws some errors – Armen Nazaryan Jun 07 '17 at 08:46
Both Gordon's query and mine should be running without error. You are probably doing something wrong in your local setup. If you can setup a demo, maybe someone can help you (e.g. www.rextester.com). – Tim Biegeleisen Jun 07 '17 at 08:47
I use phpmyAdmin directly to run sql query on server. This MySQL DB is on freehostingeu.com Unfortunatly I cant copy company date to demo data base. but I can comu the structure here – Armen Nazaryan Jun 07 '17 at 08:55
Telling me you use PHPadmin doesn't address the problem, which has to do with the actual _query_ and _data_ you are using. I won't comment further. – Tim Biegeleisen Jun 07 '17 at 08:56
OK, thabk you for trying anyway. – Armen Nazaryan Jun 07 '17 at 09:17

Gordon Linoff · Answer 2 · 2017-06-07T11:35:31.350

0

You can use union all and group by:

SELECT MAX(cisloDoch) as cisloDoch,
       MAX(presDoch) as presDoch,
       MAX(operatorDoch) as operatorDoch,
       MAX(cisloVyk) as cisloVyk,
       MAX(JMENO) as JMENO
FROM ((SELECT TIME_TO_SEC(SUM(odchodDoch - prichodDoch)) AS cisloDoch, 
              SUM(prestavkaDoch) as presDoch, operatorDoch,
              NULL as cisloVyk, NULL as JMENO
       FROM dochazkaVyroba 
       WHERE operatorDoch IN ('x', 'y', 'z') AND
             datumDoch BETWEEN '2017-06-01' AND '2017-06-02'
       GROUP BY operatorDoch
      ) UNION ALL
      (SELECT NULL NULL, NULL, SUM(CAS) as cisloVyk, JMENO 
       FROM produkce 
       WHERE JMENO IN ('x', 'y', 'z') AND
             datum BETWEEN '2017-06-01' AND '2017-06-02'
       GROUP BY JMENO
      )
     ) op
GROUP BY COALESCE(operatorDoch, JMENO);

edited Jun 07 '17 at 11:35

answered Jun 06 '17 at 18:22

Gordon Linoff

1,242,037
58
646
786

unfortunately this isnt option too I got errors. – Armen Nazaryan Jun 06 '17 at 18:39
An expression was expected. (near "(" at position 183) Unexpected token. (near "(" at position 183) Unexpected token. (near "(" at position 184) This type of clause was previously parsed. (near "SELECT" at position 185) Unexpected token. (near ")" at position 536) Unexpected token. (near ")" at position 765) Unexpected token. (near "op" at position 767) This type of clause was previously parsed. (near "GROUP BY" at position 771) Unrecognized statement type. (near "GROUP BY" at position 771) – Armen Nazaryan Jun 06 '17 at 18:40
@ArmenNazaryan . . . There is no obvious problem in the query, as far as I can see. – Gordon Linoff Jun 07 '17 at 02:06
I dont understand either but it throws error like that. MySQLi 5.7.18 – Armen Nazaryan Jun 07 '17 at 08:42

Combining Two Queries Without the Use of Certain Functions

2 Answers2