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.
select name, games, goals
from tblMadrid where name = 'ronaldo'
union
select name, games, goals
from tblBarcelona where name = 'messi'
ORDER BY goals
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;
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
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;
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
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
Source: Stackoverflow.com