Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I have 3 main tables: category, tag, url

category and tag have a MANY_MANY relationship (category_tag).

tag and url have a MANY_MANY relationship (url_tag).

I would like for a given category to retrieve all the linked urls.

What is the easiest, most efficient way to do this?

Could I maybe build a "VIEW"? What would be the query?

I am using MySQL.

share|improve this question
    
JOIN category to tag and tag to url - this is a basic SQL query. –  HLGEM Nov 19 '12 at 16:18
    
Does this look good? CREATE VIEW category_url AS (SELECT DISTINCT category.id AS category_id, url.id AS url_id FROM category LEFT JOIN category_tag ON category_tag.category_id=category.id LEFT JOIN url_tag ON url_tag.tag_id = category_tag.tag_id LEFT JOIN url ON url.id = url_tag.url_id) –  nute Nov 19 '12 at 16:53

2 Answers 2

Encapsulating the queries into Views or not, is your choice. For every one of the below queries, you can create a view:

CREATE VIEW category_url AS
  ( Query
  ) ;

If you want only the IDs of the categories and URLs, the simplest (joining only 2 tables) is this - no need for joining the tag table, as long as the foreign key constraints are defined:

    SELECT DISTINCT 
        ct.category_id,
        ut.url_id 
    FROM  
        category_tag AS ct 
      JOIN 
        url_tag AS ut
          ON ut.tag_id = ct.tag_id 

or (the equivalent) which can be easily extended to include the number of tags relating a category with a url:

    SELECT 
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags         --- optional
    FROM  
        category_tag AS ct 
      JOIN 
        url_tag AS ut
          ON ut.tag_id = ct.tag_id 
    GROUP BY 
        ct.category_id,
        ut.url_id 

If you also want columns from the category and url tables as well (which is very probable), you need to join those tables, too:

    SELECT 
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags,        --- optional

        c.*,                                       --- the columns from `category`
        u.*                                        --- and `url` that you need
    FROM  
        category_tag AS ct 
      JOIN 
        url_tag AS ut
          ON ut.tag_id = ct.tag_id 

      JOIN 
        category AS c 
          ON c.id = ct.category_id
      JOIN 
        url AS u
          ON u.id = ut.url_id

    GROUP BY 
        ct.category_id,
        ut.url_id 
share|improve this answer
    
while you might be correct, this is not very useful. In practice you would want the info not just the ids. –  JamesRyan Nov 20 '12 at 14:47
    
@James: edited. –  ypercube Nov 20 '12 at 15:59

I created the following VIEW:

CREATE VIEW category_url AS
(SELECT DISTINCT category.id AS category_id, url.id AS url_id 
FROM category
JOIN category_tag ON category_tag.category_id=category.id
JOIN url_tag ON url_tag.tag_id = category_tag.tag_id
JOIN url ON url.id = url_tag.url_id)
share|improve this answer
    
I don't know why I picked LEFT JOIN. Would INNER JOIN be more appropriate? –  nute Nov 19 '12 at 17:17
    
yes left joins are inappropriate as they will give superflous rows. You only want to use them where for example you want to list a category that has no urls and then you should only need 1 left join among the rest as inner joins. –  JamesRyan Nov 19 '12 at 17:50
    
Edited my answer. Is this a good solution now? –  nute Nov 20 '12 at 8:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.