CODEWITHSUNDEEP
phpmysql
user1912080
user1912080

Reputation: 65

select rows which have set of values in another table

I have following problem.

I have filter of users in my project, and I want Select from database only users which have in table inastrument all of instruments specified by $fm_instrument.

table user:

id_user   |  user_type   |
--------------------------
|    1    |      2       |

table instrument looks like this:

| id_user_instrument   |  id_user     | instrument_title |
----------------------------------------------------------
|            1         |      1       |        organ     |
|            2         |      1       |        flute     |
|            3         |      1       |        piano     |

EXAMPLE: filter returns $fm_instrument='flute,organ'

And I doing this:

if(isset($fm_instrument) && $fm_instrument != '')
{
$instrument_arr = explode(',',$fm_instrument);
$instrument_sql = '';
foreach ($instrument_arr as $i=>$val) // pro kazdy nastroj
{
    if($i != 0) {$instrument_sql.=" AND ";}
    $instrument_sql .= " (  instrument_title='".$val."' ) ";
}
$instrument_sql_part = " AND ($instrument_sql)";
}    

$user_sql ="SELECT * FROM `user` AS pu 
LEFT JOIN instrument AS i USING(`id_user`) WHERE user_type=2 $instrument_sql_part ";

But it doesn't work for more than one instrument.

I'm sure that there is easier solution with right mysql_query, but my knowledges of mysql are not so good.

Thank you for your patience with me.

Upvotes: 2

Views: 66

Answers (1)

Sir Rufo
Sir Rufo

Reputation: 19106

You should give this a try

SELECT 
  *
FROM 
  users
WHERE
  user_type = 2
AND
  id_user IN 
    ( SELECT 
        id_user 
      FROM (
        SELECT 
          id_user, 
          GROUP_CONCAT( instrument_title ) instrument_titles
        FROM 
          instrument 
        WHERE 
          FIND_IN_SET( instrument_title, 'flute,organ' )
        GROUP BY 
          id_user ) tmp
      WHERE 
        LENGTH( instrument_titles ) = LENGTH( 'flute,organ' ) );

SQL Fiddle DEMO

Upvotes: 2

Related Questions