how to search multi-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 search muti-cat eg:

Given some p_id (property id) I want to search match those id video

need all p_id in property_video and 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 this part in(12,15) must change to "and" but I do not know how to make it work.

Do you wish to match videos having _both_ `pv_p_id` 12 and 15, or _either_ 12 or 15? — Michael Berkowski, Oct 18 '12 at 02:16
thx for your help :),i make a categories checkbox table for user to muti-select the categories, so i want to search user tick categories, so i think need to usee "and" all p_id — user1754924, Oct 18 '12 at 02:27
i wish to match videos having both pv_p_id 12 and 15 and....(user selected) — user1754924, Oct 18 '12 at 02:28
This is related: http://stackoverflow.com/questions/639531/mysql-search-in-all-fields-from-every-table-from-a-database — Ashwin A, Oct 18 '12 at 05:52

davesnitty · Accepted Answer · 2012-10-18T03:39:29.233

Here, we will join property_video and video on the column that indicates video ID. The where condition allows us to limit the result only to videos that have the 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 the video ID and then only keeping the videos that have 2 distinct properties (i.e. must have both, not just one), you achieve the multi-category requirement. This assumes that your method to build the syntax of the query gives you the ability to adjust the number in the HAVING clause to match the number of distinct properties in your intended filter.

score 0 · Answer 2 · answered Oct 18 '12 at 02:20

0

i think you need to use innerJoin, Try this out.

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;

answered Oct 18 '12 at 02:20

d3bug3r

2,492
3
34
74

thanks for your help but i want to match all p_id like pv_p_id=12 and pv_p_id=15 and ..... – user1754924 Oct 18 '12 at 02:22
If you're using JOIN syntax, you need an ON condition to specify the logic for the join – davesnitty Oct 18 '12 at 03:29
sorry guys i used with Zend DB model, which the above query can be written in simpler way.. – d3bug3r Oct 18 '12 at 06:37

score 0 · Answer 3 · answered Oct 18 '12 at 02:21

0

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 that you are looking for videos with property IDs 12 or 15.

answered Oct 18 '12 at 02:21

Russell Gutierrez

1,372
8
19

but p_id is dynamic, depend on user tick howe many categories – user1754924 Oct 18 '12 at 02:33
then you will have to modify the query in your codes. you will have to dynamically set the values inside `in( )` during runtime. – Russell Gutierrez Oct 18 '12 at 02:37
tested, in(12,15); <- p_id= 15 not in property_video record, but still show the record property_video: pv_v_id, pv_p_id 1, 105 1 , 99 – user1754924 Oct 18 '12 at 02:49

how to search multi-categories in mysql

3 Answers3