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 tblBarcelona I want to have a query that gives me the following: I tried to follow this logic: Multiple select statements in Single query but the following code did not

SQL: Two select statements in one query

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

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

Advertisement

Answer