Duplicate columns with inner Join
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;
How to remove duplicate columns from join in SQL
Since you are querying the table with '*', you will always get all the columns in both tables. In order to omit this column, you will have to manually name all columns you DO want to query. To address your other need, you need to simply insert a dummy column to each clause in the union query. Below is an example that should work to allow for what you want -
SELECT customer.customerid, customer.customername, customer.customeraddress, newspapername, magazinename, enddate, publishedby
FROM customer
INNER JOIN
(select customerid, newspapername, null Magazinename, enddate, n.publishedby
from newspapersubscription ns, newspaper n
where publishedby in(select publishedby
from newspaper
where ns.newspapername = n.NewspaperName)
UNION
select customerid, null newspapername, Magazinename, enddate, m.publishedby
from magazinesubscription ms, magazine m
where publishedby in(select publishedby
from magazine
where ms.Magazinename = m.MagazineName))
on customer.customerid = customerid
ORDER BY customer.customerid;
SQL: Remove duplicate columns when joining tables with large number of columns
If the only duplicate columns are the JOIN
columns, you can fix this with USING
:
SELECT *
FROM TABLEA A INNER JOIN
TABLEB B
USING (id)
WHERE <SOME_CONDITION>;
Remove duplicate columns after using join on in sql
Solution 1:
Specify the column names like this
SELECT
t1.Name, t1.ItemNum, t1.TicketNum
FROM (
SELECT TOP 100
Name, ItemNum, TicketNum
FROM [dbo].[dd]
) t1
INNER JOIN (
SELECT TOP 100
TicketNum, ItemNum
FROM [dbo].[dd]
) t2 ON t1.ItemNum = t2.ItemNum
AND t1.TicketNum = t2.TicketNum
Solution 2:
SELECT t1.* FROM (
SELECT TOP 100
Name, ItemNum, TicketNum
FROM [dbo].[dd]
) t1
INNER JOIN (
SELECT TOP 100
TicketNum, ItemNum
FROM [dbo].[dd]
) t2 ON t1.ItemNum = t2.ItemNum
AND t1.TicketNum = t2.TicketNum
mysql: duplicate column name on join with subquery
In the subselect of your join, you are selecting a.controlno and by t.* t.controlno.
You should provide an alias for one of the selected columns. In your case a.controlno. This is necessary, because the table aliases of the inner select are lost, when accessing it from the outer one.
The statement below should work, if there aren't any other duplicate column names in test and the set of used columns from applicant.
SELECT sp.testno, sp.companyid, st.*
FROM sponsor AS sp
LEFT JOIN
(
SELECT a.sponsorempno, (CASE WHENt.companyid IS NULL OR t.companyid = '' THEN'aa' ELSE t.companyid END) agncy, a.controlno as a_controlno, a.tnpl, t.*
FROM applicant AS a
LEFT JOIN
test AS t
ON a.controlno = t.controlno
) AS st
ON sp.testno = st.testno
SQL problems duplicate column name using inner join and missing right parenthesis
For the first query, the GROUP BY
is inconsistent with the SELECT
. Plus, the SUM()
expression is not correct:
SELECT ACCOUNT.BillNo, UPPER(Non_resident) as Non_resident,
SUM(ConsumptionCharge + SupplyCharge) AS TotalCharge
FROM BILL JOIN
ACCOUNT
ON ACCOUNT.BILLNO = BILL.BILLNO
WHERE Non_resident = Upper('Yes') AND to_char(CreatedDate,'mm') = '09'
GROUP BY ACCOUNT.BILLNO;
Related Topics
Find Top 10 Latest Record for Each Buyer_Id for Yesterday's Date
Referencing a Calculated Column in the Where Clause SQL
Sqlite Get Name of Attached Databases
Are There Downsides to Using Prepared Statements
Creating Sumif Function in SQL Server 2012
Ms SQL Server: Check to See If a User Can Execute a Stored Procedure
Convert a String to a Date in Access
Create SQL Server Job Automatically
Caculate Point 50 Miles Away (North, 45% Ne, 45% Sw)
There Is Already an Object Named '#Result' in the Database
Why Can't I Group by 1 When It's Ok to Order by 1
Catching SQL Injection and Other Malicious Web Requests
Postgresql, Select from Max Id
"Invalid Column Name" Error on SQL Statement from Openquery Results