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.
Advertisement
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