sql union displays duplicates

Question

image of what I want

I have tried the join on x or y and it didn't work, even the group by didn't work. What almost gave me the result is the query below

SELECT A.Id ,A.AccNo ,A.Name ,B.Id ,B.AccNo1 ,B.AccNo2 ,B.Name 
   from Table1  as A 
   left outer  join Table2 as B on A.AccNo = B.AccNo1 
union 
 SELECT A.Id ,A.AccNo ,A.Name ,B.Id, B.AccNo1, B.AccNo2, B.Name, 
   from Table1 as A 
   left outer  join Table2 as B on A.AccNo = B.AccNo2

After getting the query correct I want to show only the exceptions where there was no link between the tables and its kind of difficult if the T1.ID is repeated

Dont post images. Then post table structures is better than data exmaples — Brad, Sep 07 '18 at 11:51
Please explain what the logic is that builds up your required output — , Sep 07 '18 at 12:05

score 1 · Answer 1 · answered Sep 07 '18 at 12:08

1

You seem to want a left join:

select t1.*, t2.*
from table1 t1 left join
     table2 t2
     on t1.id in (t2.accno1, t2.accno2);

answered Sep 07 '18 at 12:08

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Sep 07 '18 at 12:06

0

Try:

SELECT A.Id ,A.AccNo ,A.Name ,B.Id ,B.AccNo1 ,B.AccNo2 ,B.Name 
from Table1  as A 
left outer  join Table2 as B 
ON A.AccNo = (CASE WHEN  A.AccNo = B.AccNo1 THEN B.AccNo1 ELSE B.AccNo2 END)

answered Sep 07 '18 at 12:06

akshay

777
4
15

score 0 · Answer 3 · answered Sep 07 '18 at 12:20

You may nest your original query, and then use max aggregate function with grouping :

SELECT Id ,AccNo ,Name, max(Id2) as Id2, max(Name2) as Name2,
       max(AccNo1) as AccNo1, max(AccNo2) as AccNo2
  FROM
(
  SELECT A.Id ,A.AccNo ,A.Name ,B.Id Id2 ,B.AccNo1 ,B.AccNo2 ,B.Name Name2
     from Table1  as A 
     left outer  join Table2 as B on A.AccNo = B.AccNo1 
  union 
   SELECT A.Id ,A.AccNo ,A.Name ,B.Id Id2, B.AccNo1, B.AccNo2, B.Name Name2
     from Table1 as A 
     left outer  join Table2 as B on A.AccNo = B.AccNo2
) q  
GROUP BY Id ,AccNo ,Name;

SQL Fiddle Demo

score 0 · Answer 4 · answered Sep 07 '18 at 12:26

Do a LEFT JOIN to return the table1 values along with matching table2 values (where t2.accno2 = t1.accno):

select t1.*, t2.*
from table1 t1
left join table2 t2
     on t1.accno = t2.accno2

Or, perhaps you want table2 values for matching accno1's as well?

select t1.*, t2.*
from table1 t1
left join table2 t2
     on t1.accno in (t2.accno1, t2.accno2)

starko · Answer 5 · 2018-09-17T12:21:42.590

0

It this way to resolve:

SELECT
 t1.id,
 t1.accno,
 t1.name,
 (
     SELECT DISTINCT
         id
     FROM
         table2
     WHERE
         accno2 = t1.accno
 ),
 (
     SELECT DISTINCT
         name
     FROM
         table2
     WHERE
         accno2 = t1.accno
 ),
 (
     SELECT DISTINCT
         accno1
     FROM
         table2
     WHERE
         accno2 = t1.accno
 ),
 (
     SELECT DISTINCT
         accno2
     FROM
         table2
     WHERE
         accno2 = t1.accno
 ) FROM
 table1 t1
 LEFT JOIN table2 t2 ON t1.accno = t2.accno1 OR t1.id = t2.id

edited Sep 17 '18 at 12:21

answered Sep 07 '18 at 12:27

starko

1,150
11
26

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – Cheri Choc Sep 10 '18 at 07:55
select t1.id, t1.accno, t1.name, (select distinct id from table2 where accno2=t1.accno), (select distinct name from table2 where accno2=t1.accno), (select distinct accno1 from table2 where accno2=t1.accno), (select distinct accno2 from table2 where accno2=t1.accno) from table1 t1 left join table2 t2 on t1.accno = t2.accno1 or t1.id=t2.id – starko Sep 10 '18 at 09:08
I change code in answer - please marked as good answer. Thanx – starko Sep 17 '18 at 12:23

sql union displays duplicates

5 Answers5