SQL Two select statements in one query

Question

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

User · Accepted 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL: Two select statements in one query

Examples related to sql

Examples related to select