Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
Thread: JOINing 3 tables within a UNION
08-06-2011, 02:05 AM #1
- Join Date
- Jan 2010
- 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:
| id | username | email | 1 | Lochie | firstname.lastname@example.org | 2 | Test | email@example.com |
| id | user_id | search_discovery_by_email | seach_discovery_by_name | 1 | 1 | 1 | 1 | 2 | 2 | 0 | 0 |
| id | hash_id | user_id | search_result_type* | 1 | [RAND] | 1 | user | 2 | [RAND] | 2 | 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:
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)
EDIT Solution works: Updated below. Kinda ugly but it works flawlessly!
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)
Last edited by iLochie; 08-06-2011 at 03:26 AM.