CODEWITHSUNDEEP
sqloracle-database
Puttsche
Puttsche

Reputation: 405

Return only unique value SQL

I'm trying to create a csv file from a SQL query. But while writing this query I encounter a problem, I try to retreive all the tableA IDs in the case where valueA = valueB, but in the case where the valueB exists several times in the tableB, I don't want to retreive the tableA ID.

SELECT tableA.id, tableB.id FROM tableA INNER JOIN tableB ON tableA.valueA = tableB.valueB

Currently my result looks like this :
tableA.id;tableB.id
------------------------
1;100
2;123
5;148
5;256
7;130

And so for this example, it would be necessary to have no value for ID 5 and to have the following result :
tableA.id;tableB.id
------------------------
1;100
2;123
7;130

Thank you in advance for your help!

Upvotes: 0

Views: 56

Answers (2)

user3315556
user3315556

Reputation: 175

You can do something like this:

select * from tableA inner join (
select * from tableB where valueB in (
select valueB  from tableB group by valueB having count(valueB) = 1 ) 
) tableBB
on tableA.valueA = tableBB.valueB

Idea is to first filter out the multiple row id from tableB and then join it with tableA.

Upvotes: 2

kfinity
kfinity

Reputation: 9091

Here's an answer which is easy to read, but can be a bit slow with large tables:

SELECT tableA.id, tableB.id 
FROM tableA 
INNER JOIN tableB 
ON tableA.valueA = tableB.valueB
where (select count(1) from tableB b2 where b2.valueB = tableB.valueB) = 1

And here's a more complicated version which only queries tableB once.

SELECT tableA.id, tableB.id 
FROM tableA 
INNER JOIN (select tableB.*, count(1) over (partition by valueB) as c from tableB) tableB 
ON tableA.valueA = tableB.valueB
 AND tableB.c = 1

Upvotes: 2

Related Questions