Search code examples
mysqlsqlcounthaving-clauserelational-division

How to get common value based on a column from a table sql

I have a table. the screenshot is given bellow:

table

There have two columns

  1. item_details_id
  2. 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.

Solution

  • If you want pay_method_ids that have all available item_details_ids, 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)