-1

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.

  • If you want to analyze only numbers in use, you should put this condition on ALL in_use, i.e. use AND instead of OR in your WHERE clause – sepupic Aug 3 '18 at 13:30
  • @sepupic The problem, is if I use AND instead of OR, in my example data set I wouldn't be shown any data since there is no entry for email. – Skitzafreak Aug 3 '18 at 13:58
  • @Skitzafreak - please update your question to include table definitions for all tables involved along with enough sample data (in the form of insert statements) that allows us to fully recreate the problem locally. – Scott Hodgin Aug 3 '18 at 15:11
2

I haven't tried this, but I would suggest moving the conditions in your WHERE clause to your joins. Because there are LEFT OUTER JOINS this should work... so something like this:

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 AND scp.in_use = 1 LEFT OUTER JOIN
    dbo.supplier_contact_faxes  AS scf ON sc.contact_id = scf.contact_id AND scf.in_use = 1 LEFT OUTER JOIN
    dbo.supplier_contact_emails AS sce ON sc.contact_id = sce.contact_id AND sce.in_use = 1

GO
  • Thank you very much. This solved the problem I was having :) – Skitzafreak Aug 3 '18 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.