1

I have a relational database (innoDB) which is a tagging system with different 'tag types'. I need to retrieve records that contain ALL tags from multiple columns but I'm having some trouble getting the result I'm after.

There is a main 'targets' table, and 2 'tags' tables. Each 'tags' table represents different 'tagtypes'.

                     my_targets
=====================================================
id     name     url      tagtype1_id    tagtype2_id
-----------------------------------------------------
1     file1     url1          1              5
2     file2     url2          4              5
3     file2     url2          2              5
4     file2     url2          2              2
5     file3     url3          4              3


    my_tagtype1            my_tagtype2
=================     ================
id      title           id     title
-----------------     ----------------
1       sdrty            1     sdfg
2       fssdf            2     wpoie
3       qweok            3     q;jej
4       awerk            4     slth
5       w;eir            5     aw;eoi

The query:

SELECT * FROM
    (SELECT t.target_name, t.target_url
    FROM my_targets t
    INNER JOIN my_tagtype1 ON t.tagtype1_id = my_tagtype1.tagtype1_id
    WHERE my_tagtype1.tagtype1_id IN (2,4)
    GROUP BY target_name
    HAVING COUNT(distinct my_tagtype1.tagtype1_id) = 2) AS t1
INNER JOIN
    (SELECT t.target_name, t.target_url
    FROM my_targets t
    INNER JOIN my_tagtype2 ON t.tagtype2_id = my_tagtype2.tagtype2_id
    WHERE my_tagtype2.tagtype2_id IN (5)
    GROUP BY target_name) AS t2

The problem

If a user selects the following tags:

     tagtype1       tagtype2
    ==========     ==========
        id             id 
    ----------     ----------
         2              5
         4

..the result is:

  target_name    target_url     target_name    target_url
=============================================================
    file2           url2           file2           url2
    file2           url2           file1           url1

And this is what it should be:

  target_name    target_url
===============================
    file2           url2

Any suggestions are greatly appreciated. Thanks

HeyBlondie
  • 11
  • 2
  • Ok thanks. I thought it was helpful to show what IS working, but can understand it might be too much information. I can also see my main problem has gotten lost. I'll work on editing... – HeyBlondie Nov 06 '18 at 13:57
  • I've hopefully made it more concise and hopefully also easier to follow. (it's also 1:20am, so I'm heading to bed). I'll edit more as and if necessary of course. – HeyBlondie Nov 06 '18 at 14:54
  • Finding rows where column values appear with all values of another column is a special case of "relational division". – philipxy Nov 06 '18 at 23:48
  • Could you elaborate please? – HeyBlondie Nov 06 '18 at 23:57
  • The more I look at this, the less intuitive it seems to be searching through the 'tagtype' tables, when the id's already exist in the my_targets table. I can always retrieve the tagtype 'titles' in a separate query. I feel that I'll still be facing the same issue regardless. – HeyBlondie Nov 07 '18 at 00:59
  • I have added an UPDATE. I now get the correct result, but there are double-ups of each column – HeyBlondie Nov 07 '18 at 02:26
  • Please do not append UPDATEs/EDITs. Edit your post to be the best presentation possible. The old version doesn't matter & is at the link 'edited'. You stil don't clearly say what your query is supposed to do. Please read & act on [mcve]. PS What did you learn from googling "relational division" & searching for the tag? What about when you googled many clear, concise precise phrasings of your question/problem/goal with & without your particular names/strings? – philipxy Nov 07 '18 at 02:51
  • Apologies. I have solved the problem I was having. I will edit the OP to focus just on the problem at hand and then I will also post my Answer. (I will check the link you sent - thank you). As to your last question; I have spent the last five days trying to solve this problem and have read virtually dozens of articles and references that didn't provide me with the solution (obviously). Posting here was my absolute last resort! – HeyBlondie Nov 07 '18 at 05:02

1 Answers1

0

I have now managed to work through the issue and solve the problem I was having. I'll do my best to provide an understanding of what was needed.

The first I noticed is that I needed the line ON t1.target_name = t2.target_name added to the end of the query. The query needs criteria for which to base the 'JOIN' on of course.

The result from the example search in the OP was then:

 target_name     target_url     target_name     target_url
=============================================================
   file2            url2          file2           url2

So I had the result I was after, but still double-up of columns.

When I tried adding GROUP BY to the end of the query, I got an error stating the column name to group by, was ambiguous. I found the explanation of that here: http://www.mysqltutorial.org/mysql-inner-join.aspx

Rather then selecting all columns/results (*), I entered the specific column names I was after.

The working query is:

SELECT t1.target_name, t1.target_url FROM
    (SELECT t.target_name, t.target_url
        FROM my_targets t
        INNER JOIN my_tagtype1 ON t.tagtype1_id = my_tagtype1.tagtype1_id
        WHERE my_tagtype1.tagtype1_id IN (2,4)
        GROUP BY target_name
        HAVING COUNT(distinct my_tagtype1.tagtype1_id) = 2) AS t1
INNER JOIN
    (SELECT t.target_name, t.target_url
        FROM my_targets t
        INNER JOIN my_tagtype2 ON t.tagtype2_id = my_tagtype2.tagtype2_id
        WHERE my_tagtype2.tagtype2_id IN (5)
        GROUP BY target_name) AS t2
        ON t1.target_name = t2.target_name

...and the final result is now this:

 target_name     target_url
=============================
   file2           url2
HeyBlondie
  • 11
  • 2