Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2012
    Posts
    12

    Question Unanswered: Join over four tables with criteria sorting

    Hello, with your help i already managed to query the result i want to have.

    I have five tables:

    journey
    category
    country
    journey_has_category
    journey_has_country

    I want to get all journeys, that have at least one category or one country.

    The Usecase is this: The User can select different categories and different countries he would like to have.
    Now i want to show him the journeys that match at least one criteria but i want it sorted by the number of hitted criterias.
    Hope thats understandable.

    I am currently using this query to get the journey, that match at least one criteria:

    Code:
    select journey.name, journey.id
                          from journey
                        inner
                          join journey_has_category
                            on journey.id = journey_has_category.journey_id
                        inner
                          join category
                            on journey_has_category.category_id = category.id
                        inner
                          join journey_has_country
                            on journey.id = journey_has_country.journey_id
                        inner
                          join country
                            on journey_has_country.country_id = country.id
                         where country.id
                               in ( countries ie. 5,1,3,6 )
                         OR
                         category.id
                               in ( categories ie. 5,3,8,6,12 )
                        group
                            by journey.name
    The question is: How can i modify this query to get the journeys sorted by hitted criteria.
    Any help will be appreciated, because i have no clue how to solve this problem.

    Should i go for two queries or is it possible to do it in one?

    €dit: I already tried to work with count(*) and different group by but i am not even close to the solution.
    Last edited by Lemmming; 04-24-12 at 20:55.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please explain "sorted by hitted criteria"

    what criteria? country and category?

    well, it would have to be one or the other or both, yes?

    are you saying you want to count the number of countries a journey belongs to? and count the number of categories it's in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    12
    Thanks for the quick respone.

    I want the query to sort by hitted criterias. That means the sum of hitted countries and categories.

    Maybe an example:

    journey A has category sport and culture and country Peru.
    journey B has category sport and culture and country Chile.

    if i search for Chile and sport i want the following result:

    journey A: 1 hitted criteria (sport)
    journey B: 2 hitted criterias (sport, Chile)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, that's what i thought
    Code:
    SELECT journey.name
         , journey.id
         , ctgy.ctgy_count
         , ctgy.categories
         , ctry.ctry_count
         , ctry.countries
      FROM journey
    INNER
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctgy_count
                  , GROUP_CONCAT(category_id) AS categories
               FROM journey_has_category
              WHERE category_id IN ( 5,3,8,6,12 )
             GROUP
                 BY journey_id ) AS ctgy
        ON ctgy.journey_id = journey.id
    INNER
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctry_count
                  , GROUP_CONCAT(country_id) AS countries
               FROM journey_has_country
              WHERE country_id IN ( 5,1,3,6 )
             GROUP
                 BY journey_id ) AS ctry
        ON ctry.journey_id = journey.id
    ORDER
        BY ctgy.ctgy_count + ctry.ctry_count DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2012
    Posts
    12
    Thanks for the query. I will take a look, analyze it and report back to you.

  6. #6
    Join Date
    Apr 2012
    Posts
    12
    Ok, the query works and you rock!

    i thought i could fit it to my needs after somebody pushed me in the right direction.

    The query now looks like this:
    I changed the inner to left joint because i also want a result if the user does not provide a category or a country.
    Code:
    SELECT journey.name
         , journey.id
         , ctgy.ctgy_count
         , ctgy.categories
         , ctry.ctry_count
         , ctry.countries
      FROM journey
    LEFT
      JOIN ( SELECT journey_id,
             COUNT(*) AS ctgy_count
                  , GROUP_CONCAT(category_id) AS categories
               FROM journey_has_category
              WHERE category_id IN ( 0 )
             GROUP
                 BY journey_id ) AS ctgy
        ON ctgy.journey_id = journey.id
    LEFT
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctry_count
                  , GROUP_CONCAT(country_id) AS countries
               FROM journey_has_country
              WHERE country_id IN (1 )
             GROUP
                 BY journey_id ) AS ctry
        ON ctry.journey_id = journey.id
    ORDER
        BY ctgy.ctgy_count + ctry.ctry_count DESC
    Since you only query the relation table there is one more problem i hope you can easily solve.

    I divided the categories in normal and top categories.
    If a category has a topcategory the id of the topcategory will be saved in the category_id field in the category table.

    So the user can select a topcategory or not. If the select one i also want all child categories to be in the search query.

    The example will look like this:

    Sport is top category and tennis and swimming are child categories.

    journey A hast swimming and Chile.

    I will look for Sports in Chile and want journey A as result.

    That means i also have to query the category table.

    I hope you understand, otherwise i will give a clearer example.

    €dit: With my basic mysql skills i already figured out that i have to hook int the query in here and that i have to get all categories that have the provided ones as topcategory.

    Code:
    SELECT journey.name
         , journey.id
         , ctgy.ctgy_count
         , ctgy.categories
         , ctry.ctry_count
         , ctry.countries
      FROM journey
    LEFT
      JOIN ( SELECT journey_id,
             COUNT(*) AS ctgy_count
                  , GROUP_CONCAT(category_id) AS categories
               FROM journey_has_category
              
              --> HOOK
    
              WHERE category_id IN ( 0 )
             GROUP
                 BY journey_id ) AS ctgy
        ON ctgy.journey_id = journey.id
    LEFT
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctry_count
                  , GROUP_CONCAT(country_id) AS countries
               FROM journey_has_country
              WHERE country_id IN (1 )
             GROUP
                 BY journey_id ) AS ctry
        ON ctry.journey_id = journey.id
    ORDER
        BY ctgy.ctgy_count + ctry.ctry_count DESC
    Last edited by Lemmming; 04-24-12 at 22:10.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Lemmming View Post
    I changed the inner to left joint because i also want a result if the user does not provide a category or a country.
    i kinda thought that might be coming next

    Code:
    SELECT journey.name
         , journey.id
         , COALESCE(ctgy.ctgy_count,0) AS ctgy_count0
         , ctgy.categories
         , COALESCE(ctry.ctgy_count,0) AS ctry_count0
         , ctry.countries
      FROM ...
    ORDER
        BY ctgy.ctgy_count0 + ctry.ctry_count0 DESC
    could you please do a SHOW CREATE TABLE for your categories table so i can see how top categories are stored
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2012
    Posts
    12
    The Create command looks like this:
    Code:
    REATE TABLE `category` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(128) NOT NULL,
      `journey_counter` int(11) DEFAULT NULL,
      `slideshow_id` int(11) DEFAULT NULL,
      `category_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fk_category_slideshow1` (`slideshow_id`),
      KEY `fk_category_category1` (`category_id`),
      CONSTRAINT `category_ibfk_1` FOREIGN KEY (`slideshow_id`) REFERENCES `slideshow` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Over night i already figured out a solution that would also work.

    I will do two queries, in the first one i will gather all categories and topcategories together and the use the query you provided to get the result.

    BUt if you can figure out how to do that in one query just go ahead.

    Btw. Thanks for the improvement with the last post. I couldn't imagine how to do that on my own.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Lemmming View Post
    I will do two queries, in the first one i will gather all categories and topcategories together and the use the query you provided to get the result.
    great! that saves me from figuring it out

    can you test this query and then show it?

    i'll incorporate it into the journeys query for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2012
    Posts
    12
    THe query will be:
    SELECT * FROM category
    WHERE
    id IN (1,2,3)
    OR
    category_id in(1,2,3)
    Since there is only one top category level and one child category level this should wok out.

    Example:
    Sport is top category with id 3
    Tennis is child category with id 5

    if i search for sport i will also get Tennis but if i only search for tennis i wouldn't get Sport.

    How can i integrate that into the previous query?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with a slight change -- to return the category id instead of all columns -- your query becomes a subquery...
    Code:
    SELECT journey.name
         , journey.id
         , COALESCE(ctgy.ctgy_count,0) AS ctgy_count0
         , ctgy.categories
         , COALESCE(ctry.ctgy_count,0) AS ctry_count0
         , ctry.countries
      FROM journey
    LEFT OUTER
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctgy_count
                  , GROUP_CONCAT(category_id) AS categories
               FROM journey_has_category
              WHERE category_id IN 
                    ( SELECT id                     
                        FROM category               
                       WHERE id IN (1,2,3)          
                          OR category_id IN (1,2,3) )
             GROUP
                 BY journey_id ) AS ctgy
        ON ctgy.journey_id = journey.id
    LEFT OUTER
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctry_count
                  , GROUP_CONCAT(country_id) AS countries
               FROM journey_has_country
              WHERE country_id IN (1 )
             GROUP
                 BY journey_id ) AS ctry
        ON ctry.journey_id = journey.id
    ORDER
        BY ctgy.ctgy_count0 + ctry.ctry_count0 DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2012
    Posts
    12
    Thank you very much!

    I fixed some small typos and its working great now.
    The query looks like this.

    Code:
    SELECT journey.name
         , journey.id
         , COALESCE(ctgy.ctgy_count,0) AS ctgy_count0
         , ctgy.categories
         , COALESCE(ctry.ctry_count,0) AS ctry_count0
         , ctry.countries
      FROM journey
    LEFT OUTER
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctgy_count
                  , GROUP_CONCAT(category_id) AS categories
               FROM journey_has_category
              WHERE category_id IN 
                    ( SELECT id                     
                        FROM category               
                       WHERE id IN (5)          
                          OR category_id IN (5) )
             GROUP
                 BY journey_id ) AS ctgy
        ON ctgy.journey_id = journey.id
    LEFT OUTER
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctry_count
                  , GROUP_CONCAT(country_id) AS countries
               FROM journey_has_country
              WHERE country_id IN (0 )
             GROUP
                 BY journey_id ) AS ctry
        ON ctry.journey_id = journey.id
    ORDER
        BY ctgy_count0 + ctry_count0 DESC
    Again, thank you for your help.
    I don't know what I would do without your help.

  13. #13
    Join Date
    Apr 2012
    Posts
    12
    Hey, its me again with one more question:

    The query will list all possible solutions, even if both join tables return NULL.
    So after the join, how can I remove the rows that have zero hits in country and category join?

    I tried with Where at the end, but he can not access the ctry_count and ctgy_count.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the WHERE clause doesn't go at the end, it goes after the FROM clause but before the ORDER BY clause...
    Code:
    WHERE ctgy_count0 + ctry_count0 > 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2012
    Posts
    12
    Thats right, but i cant put it there:
    Code:
    SELECT journey.name
         , journey.id
         , COALESCE(ctgy.ctgy_count,0) AS ctgy_count0
         , ctgy.categories
         , COALESCE(ctry.ctry_count,0) AS ctry_count0
         , ctry.countries
      FROM journey
    LEFT OUTER
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctgy_count
                  , GROUP_CONCAT(category_id) AS categories
               FROM journey_has_category
              WHERE category_id IN 
                    ( SELECT id                     
                        FROM category               
                       WHERE id IN (5)          
                          OR category_id IN (5) )
             GROUP
                 BY journey_id ) AS ctgy
        ON ctgy.journey_id = journey.id
    LEFT OUTER
      JOIN ( SELECT journey_id
                  , COUNT(*) AS ctry_count
                  , GROUP_CONCAT(country_id) AS countries
               FROM journey_has_country
              WHERE country_id IN (0 )
             GROUP
                 BY journey_id ) AS ctry
        ON ctry.journey_id = journey.id
    WHERE ctgy_count0 + ctry_count0 > 0
    ORDER
        BY ctgy_count0 + ctry_count0 DESC
    That doesn't work because then the both values are unknown.
    And i also cannot put it right after the FROM journey, because there are joins following.

    Did i miss anything essential?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •