View Full Version : JOINing 3 tables within a UNION

08-06-2011, 03:05 AM
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 | a@example.com |
2 | Test | b@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 |

*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:

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!

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)