Where clause in select statement when joining to table

Question

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

Results - Results wanted:

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:

Thank you for all your help - with that query I am getting an illegal zero length error - any idea why this is? ---- it seems to be something to do with the "" I replaced them with '' but it still only returns engineers and ignores the rest of the items like lockers. — User87797
– User87797, Commented Nov 27, 2015 at 1:11

Norbert · Accepted Answer · 2015-11-27 00:59:54Z

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.

Thanks for the answer - however I have just tried that and when I search for a specific employee name it returns only that specific employee and everything that isn't an employee. I really only want that employee returned in that situation? any idea on how/if this can be done?
And that does work - however now when I do a search like on % it only returns engineers and not other locations like lockers.
I just had to change (NVL(e.employee_name,"") to (NVL(e.employee_name,' ') and it worked :) THANK YOU!!
Ah, the famous Oracle: Empty string=null issue: Sorry about that: Should have given you the space in the string in the first try :)

Collectives™ on Stack Overflow

Where clause in select statement when joining to table

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related