Duplicate columns with inner Join

Question

SELECT 
    dealing_record.*
    ,shares.*
    ,transaction_type.*
FROM 
    shares 
    INNER JOIN shares ON shares.share_ID = dealing_record.share_id
    INNER JOIN transaction_type ON transaction_type.transaction_type_id = dealing_record.transaction_type_id;

The above SQL code produces the desired output but with a couple of duplicate columns. Also, with incomplete display of the column headers. When I change the

linesize 100

the headers shows but data displayed overlaps

I have checked through similar questions but I don't seem to get how to solve this.

change to 'from shares inner join dealing_Record on'... you have joined shares with shares — wxyz, Nov 08 '13 at 15:52
You'll need to specify the columns if you don't want `Select *`. As an aside `select *` is [considered bad practice](http://stackoverflow.com/q/65512/119477). — Conrad Frix, Nov 08 '13 at 16:18

score 22 · Accepted Answer · answered Nov 08 '13 at 16:45

You have duplicate columns, because, you're asking to the SQL engine for columns that they will show you the same data (with SELECT dealing_record.* and so on) , and then duplicates.

For example, the transaction_type.transaction_type_id column and the dealing_record.transaction_type_id column will have matching rows (otherwise you won't see anything with an INNER JOIN) and you will see those duplicates.

If you want to avoid this problem or, at least, to reduce the risk of having duplicates in your results, improve your query, using only the columns you really need, as @ConradFrix already said. An example would be this:

SELECT 
    dealing_record.Name
    ,shares.ID
    ,shares.Name
    ,transaction_type.Name
    ,transaction_type.ID
FROM 
    shares 
    INNER JOIN shares ON shares.share_ID = dealing_record.share_id
    INNER JOIN transaction_type ON transaction_type.transaction_type_id = dealing_record.transaction_type_id;

score 3 · Answer 2 · answered Nov 08 '13 at 15:54

3

Try to join shares with dealing_record, not shares again:

select dealing_record.*,
       shares.*,
       transaction_type.*
FROM shares inner join dealing_record on shares.share_ID = dealing_record.share_id
            inner join transaction_type on transaction_type.transaction_type_id=
dealing_record.transaction_type_id;

answered Nov 08 '13 at 15:54

wxyz

709
3
8

i still have duplicate records – Abiodun Nov 08 '13 at 15:57
did you tried using DISTINCT? make an sql fiddle test env to get more help – wxyz Nov 08 '13 at 15:58

score 0 · Answer 3 · answered Mar 15 '23 at 05:15

use NATURAL JOIN to avoid duplicate columns which come when you've used INNER JOIN

Let's say you've Customers table and Orders table and both have CustomerID in common

When you execute the following query:

select * from Customers c inner join Orders o on c.customerID = o.customerID;

Then you'll get the column "CustomerID" two times, one from the Customers table and the other from Orders table.

To avoid this, we need to use NATURAL JOIN.

The syntax of NATURAL JOIN is very simple.

select * from Customers NATURAL JOIN Orders;

That's it. You need not insert any condition on which column to join. You'll get the output in such a way that the output table has only one customerID column.

Duplicate columns with inner Join

3 Answers3

Linked

Related