Reputation: 367
I'm working in SQL (PostgreSQL) with a dataset where each row is a product the company sold and each column contains some information about the product, like price, size, etc. The problem here is that I have a few types of products: (i) products that are only sold outside my state; (ii) products that are sold only inside the state; (iii) products that may be sold both inside or outside the state; (iv) products that are not specified. To specify where the product may be sold, there is extra strings. We use "-IN" if we sell inside the state and "-OUT" if we sell outside the state. Besides this issue, I can't use only split_part() because there might be more than one '-' In other words, I have something like this:
product_name units price
water-out 5 50
water-out 2 20
water-in 3 21
apple-red-in 7 7
ice-out 4 2
wood-out 12 120
leather 17 6
wood-in 1 10
tuna 5 25
it-br-in 1 2
it-br-out 5 25
I want to filter only the products that are sold both inside and outside the state. Products that we have both the possibility of having '-in' and '-out' in the name. I want to get this result:
product_name units price
water-out 5 50
water-out 2 20
water-in 3 21
wood-out 12 120
wood-in 1 10
it-br-in 1 2
it-br-out 5 25
How can I do this?
Upvotes: 0
Views: 50
Reputation: 1270463
This answers the original version of the question.
Assuming that the product name has no hyphens and the only suffixes are "-in" and "-out", then split_part()
can do this:
select t.*
from t
where exists (select 1
from t t2
where split_part(t2.name, '-', 1) = split_part(t.name, '-', 1) and
t2.name <> t.name
)
Here is a db<>fiddle.
Upvotes: 1