如何查核员工是否已登记领取福利？

Question

我们有一个表单，它将fname、lname、hiredate三个参数传递到另一个页面，用于检查个人是否为公司员工。

下面的代码基于这3个参数进行验证。

Dim sqlstr As String = "SELECT fname,lname,hiredate (SELECT COUNT(*) FROM Comp WHERE comp.emp_ID = e.emp_ID) AS [exists], b.* FROM dbo.EMP e LEFT OUTER JOIN comp b ON e.emp_id=b.emp_id WHERE e.fname Like '%" & Request.QueryString("fname") & "%' And e.lname Like '%" & Request.QueryString("lname") & "%' And e.hiredate = '" & Request.QueryString("hiredate") & "'"

如果检查确认此人确实是一名员工，则下一个代码将检查他/她是否有资格获得福利：

If myDS.Tables(0).Rows.Count > 0 Then

    Dim listOfBenefits = New List(Of String) ( { _
        "CT07B", "CT081", "CT083", "SG09A", "SG10", "SC11A", "SG23", "SG23A", "SG27" _
    })

    If Not listOfBenefits.Contains(txtBenefits.Text) Then
        blMsg.Text = "This employee is not eligible to receive any of the listed benefits yet."
    End If

End If

到目前为止，这个方法运行得很好。

现在，我们正在尝试添加另一个检查，以验证某个特定员工是否已注册任何福利。

如果员工没有注册任何福利，则不需要检查其资格。

另一方面，如果员工已经注册了一些福利，那么我们将检查他/她是否有资格享受上面列出的任何福利。

到目前为止，我的检查没有起作用，因为用于确定该员工是否注册了任何福利的检查被忽略了。

我做错了什么？

以下是更新的代码，其中包括对福利注册的检查。

 Dim sqlstr As String = "SELECT fname,lname,hiredate, (SELECT COUNT(*) FROM Comp WHERE comp.emp_ID = e.emp_ID) AS [exists], b.* FROM dbo.EMP e LEFT OUTER JOIN comp b ON e.emp_id=b.emp_id WHERE e.fname Like '%" & Request.QueryString("fname") & "%' And e.lname Like '%" & Request.QueryString("lname") & "%' And e.hiredate = '" & Request.QueryString("hiredate") & "'"

If myDS.Tables(0).Rows.Count > 0 Then

    ' Ok, this individual is an employee, let's check to see if s/he has registered for any benefits.
    benefitSQL = "SELECT fname, lname, (SELECT COUNT(*) FROM benefits WHERE benefits.emp_ID = e.emp_ID) AS [exists], b.* FROM dbo.EMP e LEFT OUTER JOIN benefits b ON e.emp_id=b.emp_id "

    If CInt(exists.Value) > 0 Then
        ' Then employee has some benefits. Now, check whether one of those benefits is on the list below. '

        Dim listOfBenefits = New List(Of String) ( { _
            "CT07B", "CT081", "CT083", "SG09A", "SG10", "SC11A", "SG23", "SG23A", "SG27" _
        } )

        If Not listOfBenefits.Contains(txtBenefits.Text) Then
            lblMsg.Text = "This employee is not eligible to receive any of the listed benefits yet."
        End If
    Else

        lblMsg.Text = "This employee has not registered for any benefits yet."   
    End If
 End If

SQL，格式为：

SELECT
    fname,
    lname,
    hiredate,
    (
        SELECT
            COUNT(*)
        FROM
            Comp
        WHERE
            comp.emp_ID = e.emp_ID
    ) AS [exists],
    b.*
FROM
    dbo.EMP e
    LEFT OUTER JOIN comp b ON e.emp_id = b.emp_id
WHERE
    e.fname LIKE %@fname And e.lname LIKE %@lname%
    AND
    e.hiredate = @hiredate

Answer 1

您正在选择数据，但希望受到数据的约束：

SELECT
    e.fname,
    e.lname,
    e.hiredate
FROM
    dbo.EMP e
WHERE
    e.fname LIKE %@fname And e.lname LIKE %@lname%
    AND
    e.hiredate = @hiredate
    AND
    EXISTS (
        SELECT
            1
        FROM
            Comp
        WHERE
            comp.emp_ID = e.emp_ID
    )

您还可以对表进行内部联接：

SELECT
    e.fname,
    e.lname,
    e.hiredate,
    c.*
FROM
    dbo.EMP e
    INNER JOIN Comp c ON e.emp_ID = c.emp_ID
WHERE
    e.fname LIKE %@fname And e.lname LIKE %@lname%
    AND
    e.hiredate = @hiredate