CODEWITHSUNDEEP
sqlsqlite
Sahar Rabinoviz
Sahar Rabinoviz

Reputation: 1979

sql table join get rows form one table only

I have the following A,B with columns c1,c2

A
--
aId

B
--
bId, aId

Lets say A has the folowing rows

aid
1
3
4

and B has the following rows

bId, aId
6, 1
5, 4
10, 1

I need an output of the following

id
1
4

Issue That is, I am trying to get all rows in table A such that A.aId exists for some row in table B for column aId. I have tried using inner join but it gives me to many rows (in the above example it would give me row 1 twice).

Upvotes: 0

Views: 26

Answers (2)

Philippe Banwarth
Philippe Banwarth

Reputation: 17725

You can filter A on a subquery on B : 

select * from A where aId in (select aId from B)

If the only column you want from A is aID, you already have it in B :

select distinct aId from B

Upvotes: 1

Deepansh Sachdeva
Deepansh Sachdeva

Reputation: 1198

This query seems to get your desired results:

SELECT DISTINCT(a.aid) FROM a INNER JOIN b ON a.aid = b.aid

Upvotes: 2

Related Questions