Stack Exchange
Select column on joining 2 tables
I wanted to select columns from table1 and table 2 but I can't select them:
SELECT table1._____
    ,table2.____
    FROM
    (
    SELECT TOP 1 ALLOCATIONS_FW.VEHICLE_ID_FW
    ,ALLOCATIONS_FW.END_DATE_FW
    ,ALLOCATIONS_FW.START_DATE_FW 
    ,ALLOCATIONS_FW.LOCATION_CODE_FW 
    ,ALLOCATIONS_FW.COMPANY_CODE_FW 
    ,ALLOCATIONS_FW.VEHICLE_RECORD_NUMBER_FW
    FROM ALLOCATIONS_FW
    WHERE ALLOCATIONS_FW.START_DATE_FW = (SELECT MAX( ALLOCATIONS_FW.START_DATE_FW)
             FROM ALLOCATIONS_FW
             WHERE ALLOCATIONS_FW.VEHICLE_ID_FW IN
                    (SELECT VEHICLE_ID_FW FROM ALLOCATIONS_FW WHERE VEHICLE_ID_FW = ALLOCATIONS_FW.VEHICLE_ID_FW)
             AND ALLOCATIONS_FW.RECORD_NUMBER_FW = %RECORD_NUMBER_FW%
             )
    AND ALLOCATIONS_FW.RECORD_NUMBER_FW = %RECORD_NUMBER_FW%
    ) AS table1
    JOIN
    (
    SELECT TOP 1 ALLOCATIONS_FW.VEHICLE_ID_FW
    ,ALLOCATIONS_FW.END_DATE_FW
    ,ALLOCATIONS_FW.START_DATE_FW 
    ,ALLOCATIONS_FW.LOCATION_CODE_FW 
    ,ALLOCATIONS_FW.COMPANY_CODE_FW
    ,ALLOCATIONS_FW.DRIVER_NAME_FW 
    FROM ALLOCATIONS_FW
    WHERE VEHICLE_ID_FW=(SELECT VEHICLE_ID_FW FROM ALLOCATIONS_FW 
                            WHERE ALLOCATIONS_FW.RECORD_NUMBER_FW = %RECORD_NUMBER_FW%)
    AND END_DATE_FW = (SELECT MAX( ALLOCATIONS_FW.END_DATE_FW)
             FROM ALLOCATIONS_FW
             WHERE VEHICLE_ID_FW=ALLOCATIONS_FW.VEHICLE_ID_FW)

    ORDER BY ALLOCATIONS_FW.START_DATE_FW DESC
    ) AS OLDALLOC
    ON table1.column1=table2.column1 

I want to select table1 and table2 columns how will I do it?? Because I tried the 
table1.VEHICLE_ID_FW
table1.ALLOCATIONS_FW.VEHICLE_ID_FW

both are not working


What do you mean by that it does not work? 2. You do not have table2 in your query, the second derived table's alias is oldalloc.


your second derived table alias isn't table2, it's OLDALLOC perhaps that's the problem

on my select above... I have to get the values from table1.  when I tried to use SELECT table1._____ the columns from the table1 is not appearing

a multi - part identifier problem...

Can you try giving them alias. Like: FROM
(
SELECT TOP 1 ALLOCATIONS_FW.VEHICLE_ID_FW AS VEHICLE_ID_FW,

The answer lies in your question. you are using "table1.column1=table2.column1". Ask yourself what is column1

Try like this, You should not use an order by clause inside a subquery. I just commented. Please try this.
SELECT *
FROM (
    SELECT TOP 1 ALLOCATIONS_FW.VEHICLE_ID_FW
        ,ALLOCATIONS_FW.END_DATE_FW
        ,ALLOCATIONS_FW.START_DATE_FW
        ,ALLOCATIONS_FW.LOCATION_CODE_FW
        ,ALLOCATIONS_FW.COMPANY_CODE_FW
        ,ALLOCATIONS_FW.VEHICLE_RECORD_NUMBER_FW
    FROM ALLOCATIONS_FW
    WHERE ALLOCATIONS_FW.START_DATE_FW = (
            SELECT MAX(ALLOCATIONS_FW.START_DATE_FW)
            FROM ALLOCATIONS_FW
            WHERE ALLOCATIONS_FW.VEHICLE_ID_FW IN (
                    SELECT VEHICLE_ID_FW
                    FROM ALLOCATIONS_FW
                    WHERE VEHICLE_ID_FW = ALLOCATIONS_FW.VEHICLE_ID_FW
                    )
                AND ALLOCATIONS_FW.RECORD_NUMBER_FW = % RECORD_NUMBER_FW %
            )
        AND ALLOCATIONS_FW.RECORD_NUMBER_FW = % RECORD_NUMBER_FW %
    ) AS table1
INNER JOIN (
    SELECT TOP 1 ALLOCATIONS_FW.VEHICLE_ID_FW
        ,ALLOCATIONS_FW.END_DATE_FW
        ,ALLOCATIONS_FW.START_DATE_FW
        ,ALLOCATIONS_FW.LOCATION_CODE_FW
        ,ALLOCATIONS_FW.COMPANY_CODE_FW
        ,ALLOCATIONS_FW.DRIVER_NAME_FW
    FROM ALLOCATIONS_FW
    WHERE VEHICLE_ID_FW = (
            SELECT VEHICLE_ID_FW
            FROM ALLOCATIONS_FW
            WHERE ALLOCATIONS_FW.RECORD_NUMBER_FW = % RECORD_NUMBER_FW %
            )
        AND END_DATE_FW = (
            SELECT MAX(ALLOCATIONS_FW.END_DATE_FW)
            FROM ALLOCATIONS_FW
            WHERE VEHICLE_ID_FW = ALLOCATIONS_FW.VEHICLE_ID_FW
            )
    --ORDER BY ALLOCATIONS_FW.START_DATE_FW DESC
    ) AS OLDALLOC ON table1.column1 = table2.column1


Please try this and let me know your results. I am not able to understand why you used top 1.
