SQL UNION is not showing the results of 2nd result set if 1st result set is empty

Question

0.00/5 (No votes)

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 18-Nov-11 3:09am

rashidfarooq

Updated 18-Nov-11 3:11am

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mehdi Gholam · Accepted Answer · 2011-11-18T03:12:00

Solution 1

Why don't you rewrite your query to the following :

SQL

SELECT * FROM employees WHERE emp_first_name LIKE '%Donald%' or emp_last_name LIKE '%George%'

Posted 18-Nov-11 3:12am

Mehdi Gholam

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.