Reputation: 53
first off I am getting very confused with this sorry if this makes no sense but I will try to be as clear as I can.
I have a table for a locations which is automatically populated with the key identifier for something being stored in the location table column 'name'.
An example would be when an employee is created there Unique employee ID is stored in the name column and if a locker for an employee is created then the locker ID is stored in the name column. When this is done a primary key for the location table is created called Location ID this is stored in both the location table (obviously) and the desired table that has created the location like the employee table as an example.
The name of an employee is stored in the employee table and I have been asked to pull through this through along side all of the other details stored in the locations table.
I have performed a left join on the location ID's so that I can still see all other items in the location table like Lockers.
I then want to be able to perform a search on either the Location_id or the engineer name.
When I do this I have tried multiple different ways - I get everything show up if I search like on both with '%' but when I search an employees name I get that specific employee and everything else like lockers along with it. I don't want that.
I want it so that if you search a wildcard on both of % then you see everything, If you search like a specific name you only see the engineers with that name and if you search a location id then you only see that specific location ID or ones like it.
My current query is below:
Select l.location_id, l.address_line_1, l.address_line_2, l.address_line_3, l.address_line_4, l.postcode, l.name, e.employee_name, l.location_type, l.mapped_wh_id, l.client_code
from T_3PL_location l
LEFT JOIN t_engineer e on l.location_id = e.location_id
WHERE l.location_id LIKE '%' AND (e.employee_name LIKE '%' OR e.employee_name is null)
ORDER BY l.location_id
However when I put: WHERE l.location_id LIKE '%' AND (e.employee_name LIKE 'SCARVER' OR e.employee_name is null)
I get these results - I only wanted it to display engineer SCARVER:
AND THEN:
If I remove the e.employee_name is null as suggested this happens:
the first query of searching both on % returns this - only displays engineers nothing else:
however when searching for a specific engineer I get this:
So I would need ONE query (if possible) and to return these results.
When search both as % results are:
And when search location_id as % and employee_name as 'SCARVER' results to be:
Upvotes: 0
Views: 72
Reputation: 6084
The problem you run into is that NULL is treated special by the database (Oracle in this case, different DBMS: Different behaviour). Using % does not work with the NULL. The trick is to translate the NULL to an empty string by using the NVL function:
Select l.location_id, l.address_line_1, l.address_line_2, l.address_line_3, l.address_line_4, l.postcode, l.name, e.employee_name, l.location_type, l.mapped_wh_id, l.client_code
from T_3PL_location l
LEFT JOIN t_engineer e on l.location_id = e.location_id
WHERE l.location_id LIKE '%' AND (NVL(e.employee_name,"") LIKE '%')
ORDER BY l.location_id;
The NVL
function checks if the value is NULL, and returns the alternative (a string in this case) if turns out to be null. The string is then compared with in the LIKE
and a value should now return.
Upvotes: 3