I am creating a View to grab contact information from my database. This is the code I have for the view at the moment:
CREATE VIEW SupplierContact AS SELECT sc.first_name + ' ' + sc.last_name AS [FullName], s.supplier_name AS [Supplier], ct.contact_type_description AS [ContactType], scp.phone_number AS [Phone], scp.extension AS [Extension], scf.fax AS [Fax], sce.email AS [Email] FROM dbo.supplier_contact AS sc INNER JOIN dbo.suppliers AS s ON sc.supplier_id = s.supplier_id INNER JOIN dbo.contact_types AS ct ON sc.contact_type_id = ct.contact_type_id LEFT OUTER JOIN dbo.supplier_contact_phones AS scp ON sc.contact_id = scp.contact_id LEFT OUTER JOIN dbo.supplier_contact_faxes AS scf ON sc.contact_id = scf.contact_id LEFT OUTER JOIN dbo.supplier_contact_emails AS sce ON sc.contact_id = sce.contact_id WHERE scp.in_use = 1 OR scf.in_use = 1 OR sce.in_use = 1 GO
In the three contact method tables
phones, faxes, and emails there is a
BIT column called
in_use. It's there so the database can keep track of which phone number, fax, or email is currently in use by the contact, while retaining records of previous numbers.
The point of my
WHERE condition is to only have a value pop up if it is a number in use, but there is a bit of an issue. Let me give you an example data point to show the problem.
Table: supplier_contact first_name: John last_name: Doe contact_id: 1 Table: supplier_contact_phones contact_id: 1 phone_number: 18001112233 in_use: 1 contact_id: 1 phone_number: 18002223344 in_use: 0 Table: supplier_contact_faxes contact_id: 1 fax: 1800112234 in_use: 1 Table: supplier_contact_emails [No email entry]
If I query my View for the dataset above, I am shown both phone numbers, even though one of them has
in_use = 0. So obviously there is a problem with my
WHERE clause, but I am not sure exactly how to fix said error.