I have two tables, the first table is the primary table called Project, the project has an ID and a Unit that I am searching against. The second table is a bridge table called Project_ChecklistItems. This table bridges Project to ChecklistItems. ChecklistItems have a Project.ID they belong to, a name and a status for that Project.ID.
I need to search by Unit, and Checklist status.
I am considering an innerjoin statement with multiple where statements.
`SELECT *
FROM dbo.Project
INNER JOIN dbo.Project_ChecklistItem
ON dbo.Project.ID = dbo.Project_ChecklistItem.ProjectID
WHERE dbo.Project.Unit = 'Industrial'
OR dbo.ECN.Division = 'Automotive'
OR dbo.Project.Unit = 'SwivelChair'
And dbo.Project_ChecklistItem.Status = 'Yes''
|Project ID | Unit | |ProjctID|ChecklistItemName|Status|
| 105 | automotive | |105 | ASO | No |
| | | |105 | PR | Yes |
| | | |105 | SOP | Yes |
The problem is that I need to be able to search by one specific Checklist_Item name, or ALL Checklist_Item names.
You have issues with the where clause
First - dbo.ECN.Division cannot be used in the Where clause because it is not in the From clause. For the rest of this I'll assume it should really be Project.Unit
If you mix clauses (ANDs and ORs) in your Where clause without using brackets you will almost always end up with something you didn't intend. I assume what you actually want is all records where the where the Project.Unit is one of (Industrial, Automotive, Swivelchair) and the Status is yes, but what you have there is all records that match one of the following (Industrial - status irrelevant), (Automotive - status irrelevant) or (Swivelchair and status must be yes).
Try either of the following where clauses
WHERE
( dbo.Project.Unit = 'Industrial'
OR dbo.Project.Unit = 'Automotive'
OR dbo.Project.Unit = 'SwivelChair'
)
And dbo.Project_ChecklistItem.Status = 'Yes'
OR
WHERE dbo.Project.Unit IN ('Industrial', 'Automotive', 'SwivelChair')
And dbo.Project_ChecklistItem.Status = 'Yes'