how to get records in the following scenario

Question

I have a table like below :

node_name              id            term_name
----------------------------------------------
test1                  001           physics 
test1                  001           maths    
test1                  001           chemistry    
test2                  002           physics    
test2                  002           maths

Given a combination of term names I want to find all rows where the id set only contains exactly the given term names.

For example given the term names physics & maths my output should be like below

node_name              id            term_name
----------------------------------------------
test2                  002           physics   
test2                  002           maths

Id set 001 contains also chemistry that is why it should not be included.

Can you explain why your result would not include `test1 001 physics` and `test1 001 maths` in your example? They both match `physics` and `maths` the same way the other two rows match. What's the difference? — ChrisWue, Dec 22 '11 at 07:00

score 1 · Answer 1 · answered Dec 22 '11 at 07:07

1

Your question: get all rows where no other rows with same id but other term_names exists

SELECT * FROM <table> x WHERE
  term_name IN ('physics','maths') AND
  NOT EXISTS (SELECT * FROM <table> WHERE id=x.id AND term_name NOT IN ('physics','maths'))

answered Dec 22 '11 at 07:07

rabudde

7,498
6
53
91

amd · Answer 2 · 2011-12-22T14:24:29.483

first of all you need to parse your query to convert the '&' to SQL 'OR' operator in PHP :

//Parse the query
        $arr = explode('&',$query);
    $where = '';
//get the term count
    $count = count($arr);
    foreach($arr as $value){
    $where .= "term_name = '" . $value . "' OR";
    }
    //Remove last or
    $where = rtrim($where,'OR');

then : use L

"select node_name ,count(1) as Total from my table where $where
group by node_name
having Total =" . $count

Finally :

your query must be in this format:

select x,count(1) as total from mytable where field1 = 'term1' or field1 = 'term2' having total = 2

score 0 · Answer 3 · answered Dec 22 '11 at 14:31

One possible way to do this:

select id, node_name 
from nodes join 
  (select id, 
       count(*) 
from nodes
  where node_name in ('physics','math')
group by id
having count(*) = 2 // this is set when generating the query ) as eligible_nodes
  on nodes.id = eligible_nodes.id

how to get records in the following scenario

3 Answers3