SQL selecting rows with matching values

Question

i'm trying to put together a SELECT statement using php and sql, but i'm having a hard time trying to select the items I want.

lets say my table looks like this...

---

master_record_id     credit_id     credit_value
118                  5             Brian J
119                  5             Brian J
120                  7             Katie W
121                  5             Brian J
121                  7             Katie W
125                  7             Katie W

---

I'm trying to find which master_record_id has both Katie W and Brian J in it. So I selected for credit_value = Brian J OR Katie W and this is the result.

Based on this small selection, I can see that the answer I want is 121 but how can I select for that? I want to find the master_record_id that contains both Katie W and Brian J...

Is there a way for me to say, "SELECT the master_record_id that contains both Katie W and Brian J"?

Answer 1

You need to use a self-join:

SELECT a.master_record_id
FROM tablename a JOIN tablename b USING (master_record_id)
WHERE a.credit_value = 'Brian J'
AND b.credit_value = 'Katie W'

Answer 2

select master_record_id
from your_table
where credit_value in ('Brian J', 'Katie W')
group by master_record_id
having count(distinct credit_value) = 2

YOou have to adjust the value in the having clause to the number of values in your in clause.