Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Duplicate columns with inner Join

Tags:

sql

sqlplus

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.

like image 318
lee Avatar asked Nov 08 '13 15:11

lee


People also ask

Does inner join allow duplicates?

The answer is yes, if there are any. If there are duplicate keys in the tables being joined.

In which type of join duplicate columns are there?

Duplicate columns with inner Join.

What happens to duplicates in inner join?

When we make our first inner join with the employees in a appointed to table. Each of those duplicates is going to get multiplied by all the rows in the linking table that have the same employee ID. So the output will give you a duplicate of each of the rows in the linking table that have the employee ID of DD.


2 Answers

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;
like image 187
Alberto Solano Avatar answered Sep 29 '22 07:09

Alberto Solano


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;
like image 36
wxyz Avatar answered Sep 29 '22 08:09

wxyz