sql union displays duplicates

sql union different columns
sql union all example
sql union remove duplicates based on one column
sql union distinct
sql union merge rows
oracle sql union
union two columns sql
union columns sql

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

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);

SQL Union All display duplicate records, This query yields the results as listed in your desired output: SELECT tbl3.​Worksheet_No, tbl3.Employee_Code, tbl3.Payslip_No, tbl3.Date, tbl2  Union will remove duplicates. Union All does not. @thegunner - Union does in fact remove duplicates. I would remove the selct * from both queries and manually add columns one at a time, to both, until you find a case where you think a duplicate occurs.

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)

What happens if tables we perform UNION on have duplicate rows , Answer When you combine tables with UNION, duplicate rows will be To explain why this is the case, recall a Venn Diagram, which shows the include duplicates, certain versions of SQL provides the UNION ALL operator. The SQL UNION Operator. The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns. The columns must also have similar data types. The columns in each SELECT statement must also be in the same order.

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

SQL Union, The SQL UNION operator combines the restults of two or more So all the retrieve rows (including duplicates) have displayed in the result set. The second part of the UNION query uses the dummy WHERE condition 1=0 to return the empty result set. This empty result set is combined with the first part of the UNION part. The UNION operator simply returns the resultset by removing the duplicates. Let me know if you have any other trick to remove duplicate rows.

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)

Union clause in SQL - Querychat, "UNION queries from different tables are very practical in SQL. We can exclude duplicates, UNION ALL columns of several tables and we can use lines and The code below shows a complex query that uses JOIN, WHERE, and subqueries. Find Duplicates From a Table in SQL Server Summary : in this tutorial, you will learn how to use the GROUP BY clause or ROW_NUMBER() function to find duplicate values in a table. Technically, you use the UNIQUE constraints to enforce the uniqueness of rows in one or more columns of a table.

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

SQL UNION overview, usage and examples, This article provides overview of the SQL UNION operator, along with examples In this operation, it combines two more queries and removes the duplicates. The following example shows the use of Union in two SELECT  Introduction to SQL DISTINCT operator. The primary key ensures that the table has no duplicate rows. However, when you use the SELECT statement to query a portion of the columns in a table, you may get duplicates. To remove duplicates from a result set, you use the DISTINCT operator in the SELECT clause as follows: SELECT DISTINCT column1, column2,

UNION (Transact-SQL), You control whether the result set includes duplicate rows: The following example shows the incorrect and correct use of UNION in two  How to Find Duplicate Values in a SQL Table Posted by Tara Kirkland Generally, it’s best practice to put unique constraints on a table to prevent duplicate rows. However, you may find yourself working with a database where duplicate rows have been created through human error, a bug in your application, or uncleaned data from external sources.

SQL SERVER, As you know UNION is used to combine the multiple result sets into a single result set by removing duplicates. Let us create the same dataset  Introduction to SQL UNION operator. The UNION operator combines result sets of two or more SELECT statements into a single result set. The following statement illustrates how to use the UNION operator to combine result sets of two queries: SELECT column1, column2 FROM table1 UNION [ALL] SELECT column3, column4 FROM table2; To use the UNION operator, you write the dividual SELECT statements and join them by the keyword UNION.

UNION Clause, This example shows how UNION ALL returns all rows from both queries, without any additional filtering to eliminate duplicates. For the large result sets common  There are four basic Set Operators in SQL Server: Union Union All EXCEPT INTERSECT Union. The Union operator combines the results of two or more queries into a distinct single result set that includes all the rows that belong to all queries in the Union. In this operation, it combines two more queries and removes the duplicates.

Comments
  • Dont post images. Then post table structures is better than data exmaples
  • The posted SQL gives you a syntax error
  • It works but it gives duplicates which is what I don't want
  • Which RDBMS are you using?
  • Please explain what the logic is that builds up your required output
  • Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
  • 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
  • I change code in answer - please marked as good answer. Thanx