CODEWITHSUNDEEP
phpsqlselect
patricko
patricko

Reputation: 831

SQL selecting rows with matching values

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"?

Upvotes: 2

Views: 14068

Answers (3)

Allan Ramírez
Allan Ramírez

Reputation: 2709

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

Upvotes: 0

juergen d
juergen d

Reputation: 204746

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.

Upvotes: 2

Barmar
Barmar

Reputation: 780724

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'

Upvotes: 7

Related Questions