Click here to Skip to main content
14,207,596 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have joined two SQL Queries using SQL UNION Operator

Example:
$query1 = "SELECT * FROM employees WHERE emp_first_name LIKE '%Donald%'";

$query2 = "SELECT * FROM employees WHERE emp_last_name LIKE '%George%'";

$final_query = $query1 . " UNION " . $query2


When I Run this $final_query, it shows the results of both queries if the first query's result set is not empty. If the first query has empty result set there will be no result shown even if second query has some values in the database.

Can some one explain where is the problem.


Note: Don't suggest me the use of single query, i have a reason to use two queries.
Posted
Updated 18-Nov-11 3:11am
v2
Comments
Timberbird 18-Nov-11 9:28am
   
If you list fields to select explicitly instead of using "SELECT *", does result change?
Amir Mahfoozi 18-Nov-11 11:15am
   
What is your DBMS ? is it MySQL ?

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Why don't you rewrite your query to the following :
SELECT * FROM employees WHERE emp_first_name LIKE '%Donald%' or emp_last_name LIKE '%George%'
   
Comments
rashidfarooq 18-Nov-11 9:15am
   
I don't want to use the single query. I have a reason to use two queries. Please check my question again and suggest a better solution. Thanks
Mehdi Gholam 18-Nov-11 9:25am
   
Since you are not getting the results you want, it might be better to change your strategy.
rashidfarooq 18-Nov-11 9:48am
   
Actually I tell you the real problem. I have made a search algorithm of like this
If user enters the following string to search
"Top Ten strategies used in Computer Algorithms"
The First query will be like this
"SELECT * FROM table_name where data LIKE '%TOP%' OR '%Ten%' OR '%strategies%' OR '%used%' OR '%in%' OR '%Computer%' OR '%Algorithms%'"
and the in the second query the words that has more than 3 characters will be splitted in smaller parts.
and in the final query both of queries will be joined using UNION operator.
I want to get the results of the first query on the top. If i use a single query then because of the OR Operator in SQL Query there is no surety that the first Query's results will be on top.
I have used this strategy because the data that is in my site for search is not totally in English Language, so there will be not exact spellings for any word.
I think now you will be able to give me a better solution.
Mehdi Gholam 18-Nov-11 9:55am
   
You lucene or my article here for searching : http://www.codeproject.com/KB/database/hOOt.aspx
rashidfarooq 18-Nov-11 10:15am
   
But I am not using the .net technology for my site. I have build my site in PHP.
Mehdi Gholam 18-Nov-11 10:21am
   
You can use lucene with php.
rashidfarooq 18-Nov-11 10:36am
   
Thanks for guiding But will it work if spellings are not correct.
Mehdi Gholam 18-Nov-11 10:44am
   
Yes
rashidfarooq 19-Nov-11 6:02am
   
Thanks for suggesting me a better solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190612.1 | Last Updated 18 Nov 2011
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100