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"?
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'
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.
This is not the best way to do that because of the performance, but anyway it should work:
select master_record_id, credit_id, credit_value
from your_table
where master_record_id in (
select master_record_id from your_table where credit_value = 'Brian J')
and master_record_id in (
select master_record_id from your_table where credit_value = 'Katie W')
It will return all the master_record_id records which has both Katie W and Brian J in it