Clash Royale CLAN TAG#URR8PPP

Column was specified multiple times

I have joined two tables and I want to use GROUP BY but I am getting error.
I have tried replacing all the columns instead of * but it is not working.

GROUP BY

SELECT t.*

FROM
(select O.[CUSTADDRESSID]
,O.[ACCOUNTNO]
,O.[ADDRESSTYPE]
,O.[ADDRESSLINE1]
,O.[ISCOMMUNICATION]
,C.[CUSTOMERID]
,C.[ACCOUNTNO]
,C.[ACCOUNTGROUPID]
,C.[PREPAIDACCOUNTSTATUSID]
,C.[PREPAIDACCOUNTSTATUSDATE]

from [ISSUER].[HISTORY].[TP_CUSTOMER_PREPAIDACCOUNTS] c
full join [ISSUER].[PLAY].[TP_CUSTOMER_ADDRESSES] o
on c.ACCOUNTNO = o.ACCOUNTNO) as t
group by ACCOUNTNO


I want to remove the duplicate ACCOUNTNO from the Join result.

ACCOUNTNO

3 Answers
3

As you used t.* that means you selected all the column but used only one column in group by thats why you got that error, now if you just use accountno in selection and use having cluse for filter duplicate account no then you can use count aggregate function and below query

count

SELECT [ACCOUNTNO]
FROM
(select O.[CUSTADDRESSID]
,O.[ACCOUNTNO]
,O.[ADDRESSTYPE]
,O.[ADDRESSLINE1]
,O.[ISCOMMUNICATION]
,C.[CUSTOMERID]
,C.[ACCOUNTNO] as C_ACCOUNTNO
,C.[ACCOUNTGROUPID]
,C.[PREPAIDACCOUNTSTATUSID]
,C.[PREPAIDACCOUNTSTATUSDATE]

from [ISSUER].[HISTORY].[TP_CUSTOMER_PREPAIDACCOUNTS] c
full join [ISSUER].[PLAY].[TP_CUSTOMER_ADDRESSES] o
on c.ACCOUNTNO = o.ACCOUNTNO
) as t
group by ACCOUNTNO
having count([ACCOUNTNO])>1


But if you want your query then you have to use distinct

distinct

SELECT distinct t.*

FROM
(select O.[CUSTADDRESSID]
,O.[ACCOUNTNO]
,O.[ADDRESSTYPE]
,O.[ADDRESSLINE1]
,O.[ISCOMMUNICATION]
,C.[CUSTOMERID]
,C.[ACCOUNTNO] as C_ACCOUNTNO
,C.[ACCOUNTGROUPID]
,C.[PREPAIDACCOUNTSTATUSID]
,C.[PREPAIDACCOUNTSTATUSDATE]

from [ISSUER].[HISTORY].[TP_CUSTOMER_PREPAIDACCOUNTS] c
full join [ISSUER].[PLAY].[TP_CUSTOMER_ADDRESSES] o
on c.ACCOUNTNO = o.ACCOUNTNO) as t


You can learn about group by

ACCOUNTNO

you have specified the same ACCOUNTNO twice in your inner query

ACCOUNTNO

SELECT t.*
FROM
(
select O.[CUSTADDRESSID]
,O.[ACCOUNTNO] -- 1st occurance
,O.[ADDRESSTYPE]
,O.[ADDRESSLINE1]
,O.[ISCOMMUNICATION]
,C.[CUSTOMERID]
,C.[ACCOUNTNO] -- second occurance
,C.[ACCOUNTGROUPID]
,C.[PREPAIDACCOUNTSTATUSID]
,C.[PREPAIDACCOUNTSTATUSDATE]
from [ISSUER].[HISTORY].[TP_CUSTOMER_PREPAIDACCOUNTS] c
full join [ISSUER].[PLAY].[TP_CUSTOMER_ADDRESSES] o
on c.ACCOUNTNO = o.ACCOUNTNO) as t
--group by ACCOUNTNO


You can use alias one one of the ACCOUNTNO , example

ACCOUNTNO

,O.[ACCOUNTNO] AS ACCNO1
,C.[ACCOUNTNO] AS ACCNO2


OR alternatively

since you are doing a FULL OUTER JOIN on ACCOUNTNO
you should use COALESCE() to return either from TP_CUSTOMER_PREPAIDACCOUNTS or TP_CUSTOMER_ADDRESSES

FULL OUTER JOIN

COALESCE()

TP_CUSTOMER_PREPAIDACCOUNTS

TP_CUSTOMER_ADDRESSES

change your inner query to

select O.[CUSTADDRESSID]
,COALESCE(O.[ACCOUNTNO], C.[ACCOUNTNO]) AS ACCOUNTNO
,O.[ADDRESSTYPE]


You can try this query: it will give u duplicate accountno

SELECT [ACCOUNTNO],count([ACCOUNTNO])
FROM
(select O.[CUSTADDRESSID]
,O.[ACCOUNTNO]
,O.[ADDRESSTYPE]
,O.[ADDRESSLINE1]
,O.[ISCOMMUNICATION]
,C.[CUSTOMERID]
,C.[ACCOUNTNO] as accountno_c
,C.[ACCOUNTGROUPID]
,C.[PREPAIDACCOUNTSTATUSID]
,C.[PREPAIDACCOUNTSTATUSDATE]

from [ISSUER].[HISTORY].[TP_CUSTOMER_PREPAIDACCOUNTS] c
full join [ISSUER].[PLAY].[TP_CUSTOMER_ADDRESSES] o
on c.ACCOUNTNO = o.ACCOUNTNO) as t
group by ACCOUNTNO
having count([ACCOUNTNO])>1


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.