CODEWITHSUNDEEP
sqlselect
Berbatov
Berbatov

Reputation: 1073

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.

Upvotes: 78

Views: 605255

Answers (10)

Ibrahim Zangana
Ibrahim Zangana

Reputation: 1

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

select name,games,goals as 'most goals' from tblRealMadrid union select name,games,goal as 'most goals' from tblBarcelona

Upvotes: 0

Akash
Akash

Reputation: 74

You can use UNION in this case

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

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

edit: grammar fix

Upvotes: 0

Digital Alchemist
Digital Alchemist

Reputation: 700

In your case, the two tables have completely different structures and cannot be joined.

The UNION operator could be used. The UNION operator joins the results of two or more SELECT statements to produce a single result set. The first column in the SELECT statement is used to sort the result set.

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

Each SELECT statement must have the same number of columns and data types that are compatible. Also, if you want to keep the duplicates, use UNION ALL rather than UNION.

Upvotes: 0

Amandeep Singh
Amandeep Singh

Reputation: 79

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;

join will help you even in case when tables do not have common columns

Upvotes: 1

Ray Kiddy
Ray Kiddy

Reputation: 3606

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

Upvotes: 115

user206168
user206168

Reputation: 1025

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

Upvotes: 34

Mathusuthanan
Mathusuthanan

Reputation: 117

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

Upvotes: 2

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

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

Upvotes: 6

oxymoron
oxymoron

Reputation: 101

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;

Upvotes: 8

zedfoxus
zedfoxus

Reputation: 37129

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;

Upvotes: 4

Related Questions