I have a table. the screenshot is given bellow:
There have two columns
- item_details_id
- pay_method_id
In item_details_id there have 1,2,1 data. On the other hand pay_method_id have 1,1,3 data.
I want to get only common values of pay_method_id depending on item_details_id.
According to the given screenshot-
in item_details_id I have ‘1’ & ‘2’ value.
—– ‘1’ pay_method_id is ‘1’, ‘3’
—– ‘2’ pay_method_id is ‘1’
So I have only ‘1’ data is common of pay_method_id
If I give a clean example
suppose a product named laptop has Paypal & Skrill payment method. another product named desktop has the Skrill Payment method..
So how can I get the common payment method named skrill?
How can I get the value? I can’t understand how can I solve this.
Any help will be very helpful to me.
Advertisement
Answer
If you want pay_method_id
s that have all available item_details_id
s, you can do:
select pay_method_id from mytable group by pay_method_id having count(*) = (select count(distinct item_details_id) from mytable)
If there may be duplicate (pay_method_id, item_details_id)
, then use:
having count(distinct item_details_id) = (select count(distinct item_details_id) from mytable)