CODEWITHSUNDEEP
sqlpostgresql
functorial
functorial

Reputation: 338

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

Upvotes: 0

Views: 140

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you just want postings, then I would suggest:

select p.*
from postings p
where exists (select 1
              from postings p2
              where p2.product = p.product and
                    p2.posting_id <> p.posting_id
             );

Or, use window functions like this:

select p.*
from (select p.*,
             count(*) over (partition by p.product) as cnt
      from postings p
     ) p
where cnt > 1;

Note that these do not require the products table, because the product information is available in postings.

Upvotes: 0

Fact
Fact

Reputation: 2410

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

Upvotes: 1

Related Questions