Oracle PL/SQL: referencing to a column name in inner joined query
I have the following SQL statement:
SELECT *
FROM cars car
LEFT JOIN (SELECT *
FROM cars auto
LEFT JOIN steeringwheels sw
ON auto.steeringwheelid = sw.ID
WHERE material = 'leather') innertable
ON innertable.ID = car.ID
LEFT JOIN steeringwheels sw
ON auto.steeringwheelid = sw.ID
WHERE sw.material='plastic'
This query delivers the columns from the table "Cars" twice, but with different values for the ID from the Car table (the purpose of the query is to map the values to see what the Car.ID would be id the material would change from leather to plastic).
------开发者_JAVA技巧------------------------------
| ID | material | ID_1 | material_1 |
-------------------------------------
| 1 | leather | 4 | plastic |
| 2 | leather | 7 | plastic |
-------------------------------------
However, I would like to output only the ID columns (not the material columns), like this:
-------------
| ID | ID_1 |
-------------
| 1 | 4 |
| 2 | 7 |
-------------
I have not been able to do this, as I haven't found a way to refer to the ID column of the inner query in any way. For example
SELECT id, innertable.id
(...)
or
SELECT id, auto.id
(...)
or
SELECT id, id_1
(...)
don't seem to work. How can achieve this?
Try explicitly listing the column names of the inner table in the select statement. Like:
...(SELECT auto.ID autoid, auto.Whatever)....
and then in the main select:
SELECT innertable.autoid ....
Is this what you're after?
SELECT auto_id, steeringwheel_id
FROM cars car
LEFT JOIN (SELECT auto.ID AS auto_id, sw1.id AS steeringwheel_id
FROM cars auto
LEFT JOIN steeringwheels sw1
ON auto.steeringwheelid = sw1.ID
WHERE material = 'leather') innertable
ON innertable.auto_ID = car.ID
LEFT JOIN steeringwheels sw2
ON auto.steeringwheelid = sw2.ID
WHERE sw.material='plastic'
精彩评论