I'm trying to join two MySQL tables, and return only the unique values between them. eg.
Table1
column1|column2
-------------
c1value1|c2value1-1
c1value2|c2value1-2
c1value3|c2value1-3
Table2
column1|column2
-------------
c1value1|c2value1-1
c1value1|c2value2-1
c1value1|c2value3-1
c1value2|c2value1-2
c1value3|c2value1-3
I want to get as my result a table that only shows the intersections of rows without any duplicates where I'm joining on table1.column1 = table2.column1:
Joined Table
table1.column2|table2.column2
-----------------------------
table1.c2value1-2|c2value1-2
table1.c2value1-3|c2value1-3
Simple joins and unions and I'm ok, but this one is causing me a headache.
Edit for clarification: I don't want any results in my joined table where Table 2 has > 1 entry in column 1. I only want to get back the values from column2 for c1value2 and c1value3.
My first thought was to get the distinct count of column2 GROUP BY column1 WHERE distinct count = 1 but that's error city.
CodePudding user response：
It's not clearly stated what must be unique? If you want to generate unique pairs here you go:
SELECT DISTINCT t1.column2, t2.column2
FROM table1 t1
JOIN table2 t2 ON t1.column1 = t2.column1
CodePudding user response：
Are you trying something like this:
select t1.column2,t2.column2
from table1 t1
inner join
table2 t2 on t1.column2= t2.column2
where t1.column1 in ('c1value2', 'c1value3')
group by t1.column2,t2.column2 ;
create table table1 (
column1 varchar(50),
column2 varchar(50) );
insert into table1 values ( 'c1value1', 'c2value1-1'),
( 'c1value2', 'c2value1-2'),
( 'c1value3', 'c2value1-3');
create table table2 (
column1 varchar(50),
column2 varchar(50) );
insert into table2 values ( 'c1value1', 'c2value1-1'),
( 'c1value1', 'c2value2-1'),
( 'c1value1', 'c2value3-1'),
( 'c1value2', 'c2value1-2'),
( 'c1value3', 'c2value1-3');
Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/70
Edit: If you want the unique values of column2 of both tables based on column1 of table1, you can use a subquery for selecting the distinct values for column1 and use it in the where condition.
select t1.column2,t2.column2
from table1 t1
inner join
table2 t2 on t1.column2= t2.column2
where t1.column1 in (select distinct column1 from table1)
group by t1.column2,t2.column2 ;