sql union displays duplicates
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
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.
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 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.
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.
- 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