Problems with OR in select query

Databases
1

I have a database with information about sales opportunities for an assortment of employees.

I am trying to select all the opportunities whose status has been marked as either “Won” or “Lost” for a particular employee. My query pulls “Won” opportunities for the employee but “Lost” opportunities for ALL employees.

SELECT opp.OppID, opp.OppStatus, opp.OppReferSourceID, opp.OppValue, opp.EstimatedCloseDate, opp.oppStatus, opp.EmployeeID, opp.CompanyID, opp.OppTypeID, empl.EmployeeName, empl.EmployeeInitials, comp.CompanyName, comp.ContactName, typ.OppTypeName, typ.OppTypeCategoryID, refer.OppReferSourceName
FROM tbl_opportunities AS opp
INNER JOIN tbl_employee AS empl
USING (EmployeeID)
INNER JOIN tbl_companies AS comp
USING (CompanyID)
INNER JOIN tbl_opp_types AS typ
USING (OppTypeID)
LEFT JOIN tbl_refer_source AS refer
ON(opp.OppReferSourceID = refer.OppReferSourceID)
WHERE opp.EmployeeID = $employeeID
AND opp.EstimatedCloseDate > $floorDate
AND opp.OppStatus = 'Won'
OR opp.OppStatus = 'Lost'

$employeeID is the specific employee that I am considering.
$floorDate is the oldest date that I want to consider opportunities.

I thought that I had the OR set up correctly but I am obviously wrong. Any thoughts?

2

Thanks for the replies. It worked. I wasn’t aware I could use parenthesis like that in the query. Thank you.

“Pending” is the only other value.

I know that is not the best structure and normally I would have added a table with the associated values. I didn’t want to add additional complexity to my SQL queries for a project that needed to be turned around quickly. Honestly, I wasn’t too worried about it since a) I’m the only one working with the code, b) these options are won’t be changed and are only accessible through a drop down so there shouldn’t be an issue with a user mis-inputting them and c) this project is not available on an internet connected server so someone getting access and messing with the code is not likely.

Again, not the best practice I know but it meets the needs of this project. If those needs grow in the future, I can always alter the structure.

Thanks again for the help. It was exactly what I was looking for.

3

Well if we’re going to criticize structure… why are you storing a binary/boolean (‘Won’/‘Lost’) = (True/False) value as a string?

4

Use Parentheses to order precedence.

WHERE opp.EmployeeID = $employeeID
AND opp.EstimatedCloseDate > $floorDate
AND (opp.OppStatus = ‘Won’
OR opp.OppStatus = ‘Lost’)

(You could also use AND opp.OppStatus IN(‘Won’,‘Lost’) )

5

yeah, AND opp.OppStatus IN (‘Won’,‘Lost’) is a lot clearer

but whoa… what other values could opp.OppStatus have???