1

So, I have two tables, the first has e.g. 4 records, and the second one contains e.g. two rows. They are not related in any way, just want them to be shown together in a way shown on the picture.enter image description here I know it could be done by joining them by calculating their row number and using those, but I am curious if is there a way to do the same thing without using a variable. Thank you for your advise, and sorry for my english :)

user1777122
  • 77
  • 1
  • 5
  • You might want to have a look at this link: http://stackoverflow.com/questions/3126972/mysql-row-number. Numbering the rows like that, then JOINing on the new column *might* work. – slugonamission Sep 28 '13 at 08:19
  • 1
    They're not related in any way, yet you want to join them together? What does that even mean? Leaving databases aside for a moment, how can one ever "join" unrelated data? It's nonsensical. It's like asking how to breed a dog with an asteroid. Also, be aware that (unless you explicitly specify an ordering), tables are *unordered*; therefore it is not defined which record is "first" and your problem as described is also undefined. Besides all of which, to me the `DEPTNO` columns in your tables look suspiciously like a relationship on which they should be joined. – eggyal Sep 28 '13 at 09:50
  • You are right, I am a stupid ass :) Of course they are related, both of them reference a third table. And sorry for the misleading picture above, thats only a (wrong) sample. Thanx – user1777122 Sep 28 '13 at 12:44
  • No, you're not a stupid ass, @eggyal is clearly an engineer who in 2013 lacked imagination of the real world. A very simple example is displaying two unrelated query results in the same table to the end user. E.g. a dataclip on heroku. – Dan Jun 21 '18 at 03:35

1 Answers1

2

Yes, there is a way to do it without using variables, just stright SQL
See this demo: http://www.sqlfiddle.com/#!2/2eeb2/4

Unfortunately, MySql doesn't implement analytic functions like Oracle, Postgre and MS-SQL:
ROW_NUMBER() OVER (partition by ... order by ... ) nor rownum pseudocolumn like Oracle, and a performace of queries like this in MySql is very poor.

SELECT *
FROM (
  SELECT emp.*,
       (SELECT count(*)
        FROM emp e
        WHERE e.empno <= emp.empno
        ) rownum
  FROM emp
) e
LEFT JOIN (
   SELECT dept.*,
         (SELECT count(*)
          FROM dept d
          WHERE d.deptno <= dept.deptno
          ) rownum
   FROM dept
) d
ON e.rownum = d.rownum;
krokodilko
  • 35,300
  • 7
  • 55
  • 79