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.