SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search by using multiple joins

    The below are my tables with some rows as example and the search query. I am not sure if this is a correct solution, please could you just take a look and tell me if I should do anything in another way. I believe this can be a good example also for somebody else.

    users
    +-------+-------------+
    | userId | fullName |
    +-------+-------------+
    | 4 | Nick Piterson |
    | 4 | Nick Piterson |
    | 55 | Mark Piterson |
    +-------+--------------+

    user_categories
    +-------+-------------+
    | userId | categoryId |
    +-------+-------------+
    | 4 | 3 |
    | 4 | 2 |
    +-------+-------------+

    categories_list
    +------------+---------------+
    | categoryId | english |
    +------------+---------------+
    | 3 | marketing |
    | 2 | marketing |
    +------------+---------------+

    offering
    +--------+--------+
    | userId | offering |
    +--------+--------+
    | 4 | 3 |
    | 4 | 2 |
    +--------+--------+

    user_tags
    +--------+--------------------------------------------------+
    | userId | tags |
    +--------+--------------------------------------------------+
    | 4 | marketing, consulting, freelancer |
    | 4 | social marketing, article writing, business travelling |
    +--------+--------------------------------------------------+

    promotion_package
    +--------+-----------+
    | userId | package |
    +--------+-----------+
    | 4 | 3 |
    | 4 | 2 |
    +--------+-----------+

    SELECT DISTINCT users.userId, users.fullName
    FROM users
    INNER JOIN offering ON users.userId=offering.userId
    INNER JOIN user_tags ON users.userId=user_tags.userId
    (INNER JOIN user_categories ON users.userId=user_categories.userId
    INNER JOIN user_categories ON user_categories.categoryId=categories_list.categoryId)
    INNER JOIN promotion_package.userId=users.userId
    WHERE MATCH(users.fullName, user_tags.userTags,categories_list.english) AGAINST ('$searchTerm' IN BOOLEAN MODE)
    AND user_categories.id='$user_categories'
    AND offering.offering='$offering'
    ORDER BY promotion_package.package

    Thanks a lot!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I guess something went wrong when you copy&pasted that query? A part of one of the joins is missing

    And of course, this query only consideres users that have data in all the tables you join. Could be correct, I don't know, it depends on your database structure.

  3. #3
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have one similar question. Does it make any difference if I change the order of joins or is it exactly the same? When I make join, the next join will already consider all previous joins?
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ernest1a View Post
    Does it make any difference if I change the order of joins
    if they are all INNER JOINs, no

    with outer joins, it might
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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