flinter15 Posted May 26, 2011 Share Posted May 26, 2011 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... Link to comment https://forums.phpfreaks.com/topic/237516-mysql-newbie-with-a-tagging-query/ Share on other sites More sharing options...
suresh_kamrushi Posted May 26, 2011 Share Posted May 26, 2011 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 Link to comment https://forums.phpfreaks.com/topic/237516-mysql-newbie-with-a-tagging-query/#findComment-1220577 Share on other sites More sharing options...
flinter15 Posted May 26, 2011 Author Share Posted May 26, 2011 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?? Link to comment https://forums.phpfreaks.com/topic/237516-mysql-newbie-with-a-tagging-query/#findComment-1220593 Share on other sites More sharing options...
suresh_kamrushi Posted May 26, 2011 Share Posted May 26, 2011 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 Link to comment https://forums.phpfreaks.com/topic/237516-mysql-newbie-with-a-tagging-query/#findComment-1220675 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.