Select rows where there is a one-to-one match between two columns in same table SQL

how to combine two select queries in sql with different columns
sql merge two rows in the same table
sql merge two tables with same columns
sql combine multiple rows into one column
sql merge two tables with different fields
inner join
sql union
sql merge two tables into one

I have a SQL table with two types of ID column. e.g.

ID_1    Name          Date     ID_2
 487     Joe    09/06/2004      332
 731    Mike    06/01/2004      116
 487     Joe    09/06/2004      354
 777    Rich    01/01/2002      455
 745    Mike    06/01/2004      116

Sometimes ID_1 has multiple rows, with different values for ID_2. And vice versa, sometimes ID_2 has multiple rows, with different values for ID_1.

I would like to keep all rows where there is a one-to-one match between ID_1 and ID_2. Ideally, I would also make another table with the remaining rows, so I can easily look at them later. So the above example, only one row (the 4th one) has a one-to-one match between ID_1 and ID_2:

ID_1    Name          Date     ID_2
 777    Rich    01/01/2002      455

All of the other rows have rows where one of the IDs is duplicated. So it is basically removing any rows where either of the ID columns is duplicated at all.

I have tried using DISTINCT, but that keeps one of the duplicate rows, while I want them all removed.

p.s. this is not a question about joining tables - the example is a single table.

create table #one_to_one
(id_1 int, name varchar(20), dt date, id_2 int)

insert into #one_to_one values( 487,   'Joe',    '09/06/2004'  ,    332)
insert into #one_to_one values( 731,   'Mike',    '06/01/2004' ,     116)
insert into #one_to_one values(487,   'Joe',    '09/06/2004'  ,    354)
insert into #one_to_one values( 777,    'Rich',    '01/01/2002',      455)
insert into #one_to_one values( 745,    'Mike',    '06/01/2004',      116)


select id_1, name, dt, id_2
from (select *, count(*) over(partition by id_1) as id_1_count,
               count(*) over(partition by id_2) as id_2_count
      from #one_to_one) res
where id_1_count = 1 and id_2_count = 1;

Combine results from several SQL tables, one table. How do you combine results from more than one SQL table? How do I combine results from several SQL tables (hint: there are three ways). report this ad The data is recombined by matching columns from each table. Unions. A UNION is used to combine the rows of two or more queries into one result. To get the identical rows (based on two columns agent_code and ord_amount) once from the orders table, the following SQL statement can be used : SQL Code: SELECT DISTINCT agent_code,ord_amount FROM orders WHERE agent_code='A002';

You could use windowed COUNT:

CREATE TABLE only_one_to_one
AS
SELECT ID_1, Name, Date, ID_2
FROM (SELECT *,COUNT(*) OVER(PARTITION BY ID_1) AS ID1_cnt,
               COUNT(*) OVER(PARTITION BY ID_2) AS ID2_cnt
      FROM tab) sub
WHERE ID1_cnt = 1 AND ID2_cnt = 1;

db<>fiddle demo

DBA Study Guide, Simple loin It retrieves rows from two tables having common column. 1. EQUI IOIN 2. NON EQUI IOIN Equiloin An equi join is a join, which is, based on rows that have equivalent values for specified columns. select empno, ename, It can compare each row of the table to itself and also with other rows of the same table. Thanks guys :-) I used the below because I only cared about those two columns and not so much about the rest. Worked great. select email, login_id from table group by email, login_id having COUNT(email) > 1

Only one to one

SELECT *
  FROM Table A
 WHERE (SELECT Count(1)
          FROM Table B
         WHERE A.ID_1 = B.ID_1) = 1
   AND (SELECT Count(1)
          FROM Table B
         WHERE A.ID_2 = B.ID_2) = 1

More than one

SELECT *
  FROM Table A
 WHERE (SELECT Count(1)
          FROM Table B
         WHERE A.ID_1 = B.ID_1) > 1
    OR (SELECT Count(1)
          FROM Table B
         WHERE A.ID_2 = B.ID_2) > 1

PostgreSQL 8.4 Official Documentation, A query that accesses multiple rows of the same or different tables at one time is the city column of each row of the weather table with the name column of all rows in the cities table, and select the pairs of rows where these values match. two things about the result set: There is no result row for the city of Hayward. You can test one-to-one relationship with Group By. SELECT Column_A, Column_B FROM MyTable GROUP BY Column_A, Column_B SELECT Column_A FROM MyTable GROUP BY Column_A SELECT Column_B FROM MyTable GROUP BY Column_B. The result count should be same for above three queries. share.

this code will help you please

create table #temp (ID_1 int,name varchar(255),[Date] date,ID_2 int)
insert into #temp values (487  ,  'Joe','09/06/2004', 332)
insert into #temp values (731  , 'Mike' ,  '06/01/2004'  ,    116   )
insert into #temp values (487  , ' Joe' ,  '09/06/2004'  ,    354   )
insert into #temp values (777  , 'Rich' ,  '01/01/2002'  ,    455   )
insert into #temp values (745  , 'Mike' ,  '06/01/2004'  ,    116   )


Select * from (
Select ROW_NUMBER() OVER(ORDER BY id_1 DESC)  AS Row#,ID_1,Name,Date,ID_2 
FROM #temp

) as T 

Where Row# = 4
Drop table #temp

SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach, Employee table has a recursive foreign key column called ManagerID, which Managers and employees have their data stored in the same table. There is only one employee that does not have a manager, and that's the The third column was the title of the Manager, and that table was aliased with an m: SELECT e. Select Id_pk, col1, col2,coln from table1 MINUS Select Id_pk, col1, col2,coln from table2; You can quickly check how many records are having mismatch between two tables. The only drawback with using UNION and MINUS is that the tables must have the same number of columns and the data types must match.

Sharpening Your Advanced SAS Skills, Line 3: Unique matching rows from CLASSA and CLASSB are selected. It is best to select common columns selected from CLASSA. 1. create table mytable as 2. select name, sex from classa /* select similar columns */ 3. except is required before MYAGE if MYAGE column was created in the same PROC SQL step. Select DataFrame Rows Based on multiple conditions on columns. Select rows in above DataFrame for which ‘Sale’ column contains Values greater than 30 & less than 33 i.e. filterinfDataframe = dfObj[(dfObj['Sale'] > 30) & (dfObj['Sale'] < 33) ] It will return following DataFrame object in which Sales column contains value between 31 to 32,

Combine Table Rows Using UNION, You can use the UNION clause to combine table rows from two different queries into one result. In order to union two tables there are a couple of requirements: The number of columns must be the same for both select statements. The MATCH predicate can perform such a check. Say you have a CUSTOMER table and a SALES table. CustomerID is the primary key of the CUSTOMER table and acts as a foreign key in the SALES table. Every row in the CUSTOMER table must have a unique CustomerID that isn’t null.

SQL Server Self Join By Practical Examples, It is useful for querying hierarchical data or comparing rows within the same table​. Note that referencing the same table more than one in a query without using table aliases SELECT select_list FROM T t1 [INNER | LEFT] JOIN T t2 ON join_predicate; And the following condition matches the city of the two customers:. Example: If a pupil doesn't have any mark yet, its record will still appear, and the columns on the right will be empty (NULL in SQL). INNER JOIN will filter out records which don't match. OUTER JOIN will be the same as the left join, except records from both sides, which don't match any association, will be retained.

Comments
  • Possible duplicate of LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
  • Look up SQL Server JOIN statements
  • What does a "one-to-one" match mean? Desired results would help.
  • Are you looking for rows where the name only appears once? Because I can't see any correlation between id_1 and id_2 in your data.
  • I'm using sql-server - I've added this tag.
  • Great intuitive answare! What about performance, this can be a prob with large tables
  • This case can be implemented with query "Exists", I can not remember if a query has better performance. @Siyon.D.P