Selecting common value of one column for array elements of another column

Question

Am having the following table structure,

--------------------------------------------------------
| id   |   po_bill_details_id   |   po_id   |   status |
--------------------------------------------------------
| 1    |   18                   |   6       |   1      |
| 2    |   16                   |   7       |   1      |
| 3    |   18                   |   7       |   1      |
--------------------------------------------------------

I need to select po_bill_details_id, which will be a common value for the given array of po_id. i.e. If i give [6,7] as input for po_id, i should only get 18 from po_bill_details_id and not 16. How can i query to MySQL for this logic?

So you want to get bill id which has corresponding PO id as per entire input array. Interesting. — bonCodigo
– bonCodigo, Commented Dec 5, 2012 at 6:36

bonCodigo · Accepted Answer · 2012-12-05 06:44:46Z

2

Try the following. I am on mobile, so can't test out though. Based on the number of elements in array you can change the count.

SELECT bill_id FROM yourtable
WHERE PO_id IN (7,6) 
GROUP BY bill_d 
HAVING COUNT(DISTINCT po_id) = 2

answered Dec 5, 2012 at 6:44

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sashi Kant · Accepted Answer · 2012-12-05 06:55:22Z

1

You can try ::

select 
po_bill_details_id

from table

where po_id in (/*your array*/)

group by po_bill_details_id having count(po_id)=(/*your array's length*/)

edited Dec 5, 2012 at 6:55

answered Dec 5, 2012 at 6:35

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

3 Comments

user1726343 Over a year ago

That should be having count(po_id) > 1, no? I think the OP wants results that are duplicates once the table has been filtered by the predicates.

Sashi Kant Over a year ago

@Asad: You have answered to a a specific example OP has provided, the OP needed those duplicate values which are common to all po_id

user1726343 Over a year ago

the OP needed those duplicate values which are common to all po_id this is the part I'm having trouble understanding. By the way, po_bills_id isn't a column.

user1726343 · Accepted Answer · 2012-12-05 06:36:51Z

1

SELECT po_bill_details_id
FROM   <table>
WHERE  po_id IN ( 6, 7 )
HAVING COUNT(po_bill_details_id) > 1

answered Dec 5, 2012 at 6:36

user1726343

Comments

Andrew · Accepted Answer · 2012-12-05 06:33:09Z

-1

Do you mean

select distinct po_bill_details_id from YOUR_TABLE where po_id=6 or po_id=7;

answered Dec 5, 2012 at 6:33

Andrew

7,82813 gold badges69 silver badges121 bronze badges

Collectives™ on Stack Overflow

Selecting common value of one column for array elements of another column

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related