Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    Regular Coder
    Join Date
    Jan 2010
    Posts
    101
    Thanks
    17
    Thanked 5 Times in 5 Posts

    JOINing 3 tables within a UNION

    Hey there, I'm working on a search bar for my website, the basic idea is to gather user information contained in different tables based on the id that matches the username that a person searches for.

    The UNION I mentioned in the title is simply so that you know that I'm working within the requirements of the UNION (4 rows)

    I have 3 tables I'm looking at: users, user_profiles, user_permitted

    • users contains information about the user
    • user_profiles contains extended information about the user (this table is linked to the "users" table with a column called "user_id" that is the id of the row in users)
    • user_permitted is basically the settings that the user has set so that the site can determine if the user wants to be found in the search using certain information


    Here are the tables:

    users:
    Code:
    |  id  |  username  |      email      |
       1   |   Lochie   |  a@example.com  |
       2   |    Test    |  b@example.com  |
    user_permitted:
    Code:
    |  id  | user_id | search_discovery_by_email | seach_discovery_by_name |
       1   |    1    |             1             |            1            |
       2   |    2    |             0             |            0            |
    user_profiles:
    Code:
    |  id  | hash_id | user_id | search_result_type* |
       1   | [RAND]  |    1    |        user         |
       2   | [RAND]  |    2    |        user         |
    *This field is for the union to identify the type of search result that's returned (for all the results in the above problem, the search result would be "user"

    So here's what needs to happen in this join
    The user would be searching the "users" table by usernames LIKE their search query. The query would need to identify the corresponding profile information and "permitted" information based on the "id" of each search result. Maybe pseudo code would help:

    Code:
    select users.id,user_profiles.hash_id,users.username,user_profiles.search_result_type from the tables WHERE username LIKE (SEARCH QUERY) AND (get the row where the user_profiles.user_id is equal to the id of the row that matches the search query) AND (make sure the result can be displayed, ie. the user has permitted it (value = 1) in user_permitted)
    It's also important to understand that the "id" field of all the tables do not necessarily correspond in the same way that the user_ids do.




    EDIT Solution works: Updated below. Kinda ugly but it works flawlessly!
    Code:
    SELECT `U`.`id`,`P`.`hash_id`,`U`.`username`,`P`.`search_result_type` FROM `users` AS `U` INNER JOIN `user_permitted` AS `R` ON `U`.`id` = `R`.`user_id` INNER JOIN `user_profiles` AS `P` ON `U`.`id` = `P`.`user_id` INNER JOIN `user_vitals` AS `V` ON `U`.`id` = `V`.`user_id` WHERE (`U`.`username` LIKE 'Loch%') OR (`U`.`email` LIKE 'Loch%' AND `R`.`search_discovery_by_email` = 1) OR (`V`.`full_name` LIKE 'Loch%' AND `R`.`search_discovery_by_name` = 1)


 

Posting Permissions

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