MySQL newbie with a tagging query...

[flinter15]

Hi, I'm new to MySQL and was wondering if you could help me with whats hopefully quite a simple issue...

 

I have a database with three tables:

table1: pages with an 'pageid' field and a 'pagebody' field.

table2: tag terms with a 'tagid' and 'tagname' field.

table3: one which links the pages with their tags ('pageid' and 'tagid')

 

One page can be tagged with multiple terms and it's 'pageid' will therefore appear several times in table3 with it's various 'tagid's.

 

Now I realise that if I wanted all the 'pageid's tagged with 'tagid' 17 for example, I can just say:

 

SELECT * FROM table3 WHERE tagid = 17

 

but what if I want the all the 'pageid's that are tagged with say 17 AND 23 AND 44? So it only shows me the pages that have an association with all three tags in table3.

 

This seems like it should be simple but I can't work out a way of doing it without the same 'pageid' appearing multiple times in the results.

 

I hope this makes sense, any help would be greatly appreciated...

 

 

[suresh_kamrushi]

Hi,

i am not very clear about your question, but still suggesting one query for your problem see that it is working for you or not........

 

SELECT group_concat( DISTINCT pageid ORDER BY pageid DESC SEPARATOR '|' ) FROM table3 GROUP BY tagid 

[flinter15]

Ok, it looks like you're on to something there... I still can't quite get it working though, lets say for example table3 looks like this:

 

pageid  tagid

1 ------- 3

1 ------- 4

1 ------- 9

2 ------- 4

2 ------- 11

2 ------- 45

3 ------- 3

3 ------- 9

4 ------- 9

 

I need a query that will allow me to say 'give me all the pages tagged with 3 and 4'. The result of this query should be 1 because it's the only one tagged with both. Or, 'Give me all the pages tagged with 3 and 9. The result of this query should be 1 and 3... does this make sense?? 

[suresh_kamrushi]

This query will work for you..

 

SELECT group_concat( DISTINCT pageid ORDER BY pageid DESC SEPARATOR '|' ) FROM table3 where tagid IN ('3','4') GROUP BY tagid