Eliminate duplicates from UNIONS


Keywords:sql 


Question: 

I'm trying to create a SQL Query for a simple search bar on a social-media site. So far i have this:

SELECT DISTINCT Users.[user id], users.[first name] AS [Hit], users.[user profile hyperlink] AS [Hyperlink]
FROM users
WHERE users.[first name] LIKE '*web*'

UNION 
SELECT DISTINCT Users.[user id],  users.[last name], users.[user profile hyperlink]
FROM users
WHERE  users.[last name] LIKE '*web*'

UNION  
SELECT DISTINCT Users.[user id],  posts.[post content], posts.[post hyperlink]
FROM posts, users
WHERE posts.[post content] LIKE '*web*'
AND posts.[user id] = users.[user id]


UNION 
SELECT DISTINCT group.[group id], Group.[Group Name], group.[group page hyperlink]
FROM [Group]
WHERE Group.[Group Name] LIKE '*Web*'

UNION  
SELECT DISTINCT Users.[user id],  posts.[post content], posts.[post hyperlink]
FROM posts, users, posts_has_tags, tags
WHERE tags.[tag] LIKE '*web*'
AND posts_has_tags.[tag id] = tags.[tag id]

I dont want to add variables yet so i am fine with 'web' being searched for. However the query returns some wrong results (i think this is to do with the last union'd query) and repeats other results for different user ids.

Can anyone help?


1 Answer: 

1st two queries can be clubbed together by combining the where clauses

SELECT DISTINCT Users.[user id], users.[first name] AS [Hit], users.[user profile hyperlink] AS [Hyperlink]
FROM users
WHERE users.[first name] LIKE '*web*' or users.[last name] LIKE '*web*'

Also the 4th query returns group.[group id] as 1st column, others return Users.[user id]. Not sure how can you make distinct between two different columns, you'll get wrong data