SQL: Two select statements in one query

Question

I want to select information from two SQL tables within one query, the information is unrelated though, so no potential joints exist.

An example could be the following setup.

tblMadrid

   id | name    | games | goals     1 | ronaldo | 100   | 100     2 | benzema | 50    | 25     3 | bale    | 75    | 50     4 | kroos   | 80    | 10 

tblBarcelona

   id | name    | games | goals     1 | neymar  | 60    | 25     2 | messi   | 150   | 200     3 | suarez  | 80    | 80     4 | iniesta | 40    | 5 

I want to have a query that gives me the following:

name    | games | goals messi   | 150   | 200 ronaldo | 100   | 100 

I tried to follow this logic: Multiple select statements in Single query but the following code did not work:

USE Liga_BBVA  SELECT (SELECT name,                games,                goals         FROM   tblMadrid         WHERE  name = 'ronaldo') AS table_a,        (SELECT name,                games,                goals         FROM   tblBarcelona         WHERE  name = 'messi')   AS table_b ORDER  BY goals  

Any advice on this one? Thanks Info: The football stuff is just a simplifying example. In reality it is not possible to put both tables into one and have a new "team" column. The two tables have completely different structures, but I need something that matches the characteristics of this example.

Answer 1

You can do something like this:

 (SELECT     name, games, goals     FROM tblMadrid WHERE name = 'ronaldo')  UNION  (SELECT     name, games, goals     FROM tblBarcelona WHERE name = 'messi') ORDER BY goals; 

See, for example: https://dev.mysql.com/doc/refman/5.0/en/union.html

Answer 2

If you like to keep records separate and not do the union.
Try query below

SELECT (SELECT name,                games,                goals         FROM   tblMadrid         WHERE  name = 'ronaldo') AS table_a,        (SELECT name,                games,                goals         FROM   tblBarcelona         WHERE  name = 'messi')   AS table_b FROM DUAL