CODEWITHSUNDEEP
sqlsqlitesql-like
John Jackson
John Jackson

Reputation: 3

Filtering with given value AND on FK

I've searched but couldn't fully get a question. I also tried to execute multiple query's in my query builder of my database program but the outcome isn't what I expect. So this is my question.

Let's say I two tables, hour_entries and employees ;

hour_entries has a PK (obv.) in it, and employees has a PK and a FK (This Foreign Key refers to the PK of table 1).

Let's say the columns in the table hour_entries are HourType(Programming, Cleaning etc.), a description and TotalHours(5, 2, etc.). And the FK which is called EmployeeID.

In this other table(employees) you have just the regular information about an employee. Firstname, lastname etc..

When I execute a query like so

SELECT FirstName, LastName, JobType, Description FROM hour_entries
LEFT (OUTER) JOIN employees on employees.EmployeeID = hour_entries.EmployeeID WHERE UPPER(Description) LIKE UPPER('%Bla%') OR UPPER(HourType) LIKE UPPER('%Bla%') AND EmployeeID = 4;

This seems to give me information back which matches the check for the description and hourtype. But it also returns rows with a different EmployeeID.

I want information of the employee with the given EmployeeID. So if you would put this in an if-statement it would look like this.

if(EmployeeID == givenID && Description == "Bla" || HourType == "Bla")

If the description is not a match it can also look if the hourtype is still a match. If the employeeID is not a match I don't want it to return any row. But it still does.

Upvotes: 0

Views: 25

Answers (1)

Y.B.
Y.B.

Reputation: 3586

You need to keep all Joining conditions in On clause. In Where clause they effectively convert Left Join into Inner Join. In this particular case it should be hour_entries.EmployeeID, not employees.EmployeeID in Where. Also you miss brackets around OR:

SELECT FirstName, LastName, JobType, Description
FROM hour_entries
LEFT (OUTER) JOIN employees
    ON employees.EmployeeID = hour_entries.EmployeeID
WHERE hour_entries.EmployeeID = 4 --<- "FROM" table here, not "JOIN"
  AND (                           --<- Brackets around OR
          UPPER(Description) LIKE UPPER('%Bla%')
       OR UPPER(HourType) LIKE UPPER('%Bla%')
      );                          --<- Brackets around OR

Upvotes: 1

Related Questions