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.

9 Answers

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:

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 

The UNION statement is your friend:

SELECT a.playername, a.games, a.goals FROM tblMadrid as a WHERE a.playername = "ronaldo" UNION SELECT b.playername, b.games, b.goals FROM tblBarcelona as b WHERE b.playername = "messi" ORDER BY goals; 

You can union the queries as long as the columns match.

SELECT name, games, goals FROM tblMadrid WHERE id = 1 UNION ALL SELECT name, games, goals FROM tblBarcelona WHERE id = 2 

You can combine data from the two tables, order by goals highest first and then choose the top two like this:

MySQL

select * from ( select * from tblMadrid union all select * from tblBarcelona ) alldata order by goals desc limit 0,2; 

SQL Server

select top 2 * from ( select * from tblMadrid union all select * from tblBarcelona ) alldata order by goals desc; 

If you only want Messi and Ronaldo

select * from tblBarcelona where name = 'messi' union all select * from tblMadrid where name = 'ronaldo' 

To ensure that messi is at the top of the result, you can do something like this:

select * from ( select * from tblBarcelona where name = 'messi' union all select * from tblMadrid where name = 'ronaldo' ) stars order by name; 

select name, games, goals from tblMadrid where name = 'ronaldo' union select name, games, goals from tblBarcelona where name = 'messi' ORDER BY goals 

Using union will help in this case.

You can also use join on a condition that always returns true and is not related to data in these tables.See below

select tmd .name,tbc.goals from tblMadrid tmd join tblBarcelona tbc on 1=1;

You can use UNION in this case

select id, name, games, goals from tblMadrid union select id, name, games, goals from tblBarcelona 

you jsut have to maintain order of selected columns ie id, name, games, goals in both SQLs

as i see you want most goals in each team you can try this

select name,games,max(goals) as 'most goals' from tblRealMadrid union select name,games,max(goals) as 'most goals' from tblBarcelona