SQL Two select statements in one query

+1 vote

I want to return the newly created ID, which equals 4 while running an INSERT statement. Could you please tell me how to accomplish it using a SQL statement, or is it not possible? 

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 suggestions on this one? Thanks Information: The football information is merely a simplification. In practise, combining both tables and adding a new "team" column is not doable. I require something that closely resembles the attributes of this example despite the fact that the two tables have entirely different topologies.

Aug 20, 2022 in Database by Kithuzzz
• 38,020 points
5,945 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
0 answers

How to update two tables in one statement in SQL Server 2005?

In one operation, I want to update ...READ MORE

Aug 20, 2022 in Database by Kithuzzz
• 38,020 points
650 views
0 votes
0 answers

SQL query to select data between two dates and times

My table contains a [time] field that ...READ MORE

Aug 25, 2022 in Database by Kithuzzz
• 38,020 points
2,243 views
0 votes
0 answers

How to create a table from select query result in SQL Server 2008

I tried to build a table from ...READ MORE

Sep 2, 2022 in Database by Kithuzzz
• 38,020 points
835 views
0 votes
0 answers
0 votes
1 answer

SQL - Update multiple records in one query

Try either multi-table update syntax: UPDATE config t1 ...READ MORE

answered Sep 17, 2022 in Database by narikkadan
• 63,680 points
3,721 views
0 votes
2 answers

How to select the nth row in a SQL database table?

SELECT * FROM ( SELECT ID, NAME, ROW_NUMBER() ...READ MORE

answered Apr 23, 2020 in Database by anand
• 140 points
26,108 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,070 points
796 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4, 2022 in Database by Vaani
• 7,070 points
519 views
0 votes
1 answer

SQL SELECT WHERE field contains words

Use this query to include any of words: SELECT * ...READ MORE

answered Feb 21, 2022 in Database by Neha
• 9,020 points
19,811 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL server?

In SQL Server, it is possible to insert ...READ MORE

answered May 30, 2022 in Others by anisha
• 140 points
577 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP