Search code examples
sqloracle-database

Drop Column from Selection After Join

I am trying to drop a number of columns after joining three tables. I came across a very useful SO post: SQL exclude a column using SELECT * [except columnA] FROM tableA?

However I can't get the solution I found therein (the top answer) to work for my case; joining multiple tables, and want to drop the key that is used for the second and third tables, as well as some other columns.

Here is a simplified case whereby I'm just attempting to drop the key of the second table, which comes out as mykey_2:

SELECT * INTO Temptable
FROM
table_1 INNER JOIN table_2 ON 
table_1.mykey=table_2.mykey INNER JOIN 
table_3 ON table_1.mykey= table_3.mykey
WHERE table_1.A_FIELD = 'Some Selection'
ALTER TABLE Temptable
DROP COLUMN table_2.mykey
GO
SELECT * FROM Temptable
DROP TABLE Temptable

My console is giving me the error "ORA-00933: SQL command not properly ended".

How can I achieve this dropping of specific columns from the final selection?

For information I'm querying an Oracle database using Toad.

Solution

  • With only mykey conflicting

    To solve the problem you explicitely ask for in your question ("Why does it give me a syntax error?"), @Alex Poole's comment is the answer: GO is not Oracle, just remove it.

    But then you will get what Krzysztof Madejski's answers for:
    the USING will work as long as the join column (mykey) is the only column which has the same name over multiple tables:

    create table Temptable as
SELECT *
FROM
table_1
INNER JOIN table_2 USING (mykey)
INNER JOIN table_3 USING (mykey)
WHERE table_1.A_FIELD = 'Some Selection';

    To remove other columns

    If you've got other columns with a duplicate name over two tables, you'll have to first make them unique:

    • either by renaming them as you did for the result of the select:
      create table table_3_r as select *, col as col3 from table_3; alter table table_3_r drop column col; /* And do the SELECT on table_3_r instead of table_3 */; drop table table_3_r;
    • or by using the creative, Oracle-specific answer that Felypp Oliveira gave to the question you refer with PIVOT:
      as PIVOT requires you to tell which values of the pivot column can generate a pseudo-column, one just have to list 0 values to get 0 column.
    • or write a dedicated, reusable function as proposed by Chris Saxon on Oracle's All Things SQL blog
    The PIVOT way
    WITH table_3_r as (SELECT * FROM table_3 PIVOT (max(1) FOR (a_field) IN ()))
SELECT *
FROM
table_1
INNER JOIN table_2 USING (mykey)
INNER JOIN table_3_r USING (mykey)
WHERE table_1.A_FIELD = 'Some Selection';

    Here in a fiddle.