How to search multiple categories in mysql

Question

video

v_id, v_name 

property:

 p_id, p_name 

property_video:

 pv_id, pv_v_id, <- video id pv_p_id, <- property id 

I want to look for muti-cat, for example:

Given some p_id (property id), I want the search to match those id videos

all p_id is needed in the property_video property and the same pv_v_id

then mysql wrote:

 SELECT a.*, b.* FROM video as a, property_video as b WHERE a.v_id = b.pv_v_id and b.pv_p_id in(12,15) GROUP BY a.v_id; 

I know that this part in(12,15) should change to "and", but I don’t know how to make it work.

Answer 1

Here we attach the_video property and the video in the column that indicates the identifier of the video. The where clause restricts the result to only videos that contain properties contained in the IN clause.

 SELECT v.v_id FROM property_video pv JOIN video v ON pv.pv_v_id = v.v_id WHERE pv.pv_p_id IN (12,15) GROUP BY v.v_id HAVING count(distinct pv.pv_p_id) = 2 

By grouping by video id, and then saving only those videos that have 2 different properties (i.e. there must be one or more than one), you achieve the requirement for several categories. This assumes that your method of constructing the query syntax gives you the ability to customize the number in the HAVING clause according to the number of different properties in the filter you select.

Answer 2

I think you need to use innerJoin, try this.

 SELECT a.*, b.* FROM video as a, InnerJoin property_video as b WHERE a.v_id = b.pv_p_id and b.pv_p_id in(12,15) GROUP BY a.v_id; 

Answer 3

 select a.*, b.*, c.* from video as a, property_video as b, property as c where a.v_id = b.pv_v_id and c.p_id = b.pv_p_id and c.p_id in (12,15); 

Assuming you're looking for videos with property identifiers 12 or 15.