Skip to content
Advertisement

Select rows which have a field in common with another row

I have two tables: products and postings. A product is a consumer product (ex. iPhone X), and a posting is a listing for a product on an online marketplace (ex. eBay posting). A single product has zero or more associated postings.

Is there any way to select only postings which have a “sibling”? ie. Select all postings whose product column is equal to any other postings’ product column.

SELECT * FROM postings a
INNER JOIN products b ON a.product = b.id
WHERE COUNT(b) > 0

Advertisement

Answer

I am wondering your inner join should only do the trick, but in case I am missing something you can try this

With a as 
(
SELECT a.*,b.*, count(*) over(Partition by b.id) cnt 
FROM postings a
INNER JOIN products b ON a.product = b.id
)
Select * from a where cnt > 0
Advertisement