none
Query for multiple columns from multiple tables in a single database

    Question

  • I want to know is if AbsencePolicyId = 31 was used by EmployeeId that is not assigned to AccraulPolicyId = 21

    SELECT Absence.EmployeeId,  Absence.AbsencePolicyId,  EmployeeAccrualPolicy.EmployeeId,  EmployeeAccrualPolicy.AccrualPolicyId
    FROM Absence, EmployeeAccrualPolicy
    Where EmployeeAccrualPolicy.EmployeeAccrualPolicyId != 21 and Absence.AbsencePolicyId = 31

    The query gave me any transaction assigned to AbsencePolicyId = 31 that was not assigned to EmployeeAccrualPolicyId != 21 but assigned to any other EmployeeAccraulPolicyId

    I need a query that will look at the transaction assigned to AbsencePolicyId = 31, then look at the EmployeeId associated to it, then see if that EmployeeId is linked to EmployeeAccrualPolicyId = 21 or not. 

    I read information on your TechNet site in regards to "Join Tables on Multiple Columns (Visual Database Tools)" but not sure I understand.

    The AbsencePolicy table that holds the Absence Policy information in a column called "AbsencePolicyId"  (AbsencePolicyId = 31)

    The AccrualPolicy table that holds the Accrual Policy information in a column called  "AccrualPolicyId"  (AccrualPolicyId = 21)

    The data that is recorded when an Employee (EmployeeId) uses a certain Absence Policy (AbsencePolicyId) is stored in the Absence Table.

    The EmployeeAccrualpolicy table tells us what accrual (AccrualPolicyId) each employee is assigned to.

    Wednesday, October 30, 2013 7:18 PM

Answers

  • Please have a look at the sticky posts at the top of the forum.  They provide useful suggestions regarding how to post questions and provide useful information about your issue.  In particular, it is very helpful to post the DDL of the tables involved, sample data in the form of insert statements, as well as expected outputs.  It is also important to know what version of sql server you are using since functionality varies by version.

    Next, you are apparently learning (or have learned) some bad habits.  You should be using the join syntax for joining tables within a query.  Trying to cram the join logic as well as the filter logic into the where clause only makes the process of writing and debugging your query more difficult. 

    Next, you want to know "if absencepolicy was used .... ".  You ask a question that is true or false, but we generally deal with rows in resultsets in tsql.  Do you really want a yes/no answer or is your goal to "see" something (for argument's sake, "absences that are not allowed")?  The way you phrase your question will influence the responses, and it may do so in a way that only confuses poster and responder.

    Lastly, I think you make things harder than needed.  Or perhaps you've worded your question oddly.  Absence contains both EmployeeID and AbsencePolicyID.  You can join Absence directly to EmployeeAccrualPolicy on EmployeeID.  So it seems that the following should be a good start:

    select ...
    from dbo.Absence as AB inner join dbo.EmployeeAccrualPolicy as EAP
    on AB.EmployeeId = EAP.EmployeeID
    where AB.AbsencePolicyID = 31 and EAP.EmployeeAccrualPolicyID <> 21
    order by ...;

    Wednesday, October 30, 2013 8:30 PM
  • I greatly appreciate your help and your time on this matter. The next time I write on a Forum I will take into consideration the tips you have mentioned above.

    after countless hours of digging one of our programmers and myself finally got the results that we wanted out of the database.

    I needed a query that would look at the
    “Absence” table
    and see the
    “EmployeeId”
    associated to an
    “AbsencePolicyId”(AbsencePolicyId 31 is FML Sick Leave) 
    then look at the
    “Employeeaccrualpolicy” Table
    check to see if
    “EmployeeId”
    is assigned to
    EmployeeAccrualPolicyId = 21 or not.


    Below is the query to run to find out what employees are stopping this from happening (Thanks Nancee for your amazing help)


    select EmployeeCode from Employee where
    EmployeeId in
    (select distinct EmployeeId from EmployeeAccrualPolicy
    where AccrualPolicyId != 21 and
    EmployeeAccrualPolicy.EmployeeId in (select distinct employeeid from Absence
     where AbsencePolicyId = 31)
    and IsActive = 1)
    and EmployeeId Not in (select distinct EmployeeId from EmployeeAccrualPolicy where
    AccrualPolicyId = 21)
    order by EmployeeCode

    here is another one that seemed to work also that was simplified

    select EmployeeCode from Employee where
    EmployeeId in 
    (select distinct employeeid from Absence where AbsencePolicyId = 31)
    and EmployeeId Not in 
    (select distinct EmployeeId from EmployeeAccrualPolicy where AccrualPolicyId = 21)
      order by EmployeeCode

    Wednesday, October 30, 2013 11:19 PM

All replies

  • Please have a look at the sticky posts at the top of the forum.  They provide useful suggestions regarding how to post questions and provide useful information about your issue.  In particular, it is very helpful to post the DDL of the tables involved, sample data in the form of insert statements, as well as expected outputs.  It is also important to know what version of sql server you are using since functionality varies by version.

    Next, you are apparently learning (or have learned) some bad habits.  You should be using the join syntax for joining tables within a query.  Trying to cram the join logic as well as the filter logic into the where clause only makes the process of writing and debugging your query more difficult. 

    Next, you want to know "if absencepolicy was used .... ".  You ask a question that is true or false, but we generally deal with rows in resultsets in tsql.  Do you really want a yes/no answer or is your goal to "see" something (for argument's sake, "absences that are not allowed")?  The way you phrase your question will influence the responses, and it may do so in a way that only confuses poster and responder.

    Lastly, I think you make things harder than needed.  Or perhaps you've worded your question oddly.  Absence contains both EmployeeID and AbsencePolicyID.  You can join Absence directly to EmployeeAccrualPolicy on EmployeeID.  So it seems that the following should be a good start:

    select ...
    from dbo.Absence as AB inner join dbo.EmployeeAccrualPolicy as EAP
    on AB.EmployeeId = EAP.EmployeeID
    where AB.AbsencePolicyID = 31 and EAP.EmployeeAccrualPolicyID <> 21
    order by ...;

    Wednesday, October 30, 2013 8:30 PM
  • I greatly appreciate your help and your time on this matter. The next time I write on a Forum I will take into consideration the tips you have mentioned above.

    after countless hours of digging one of our programmers and myself finally got the results that we wanted out of the database.

    I needed a query that would look at the
    “Absence” table
    and see the
    “EmployeeId”
    associated to an
    “AbsencePolicyId”(AbsencePolicyId 31 is FML Sick Leave) 
    then look at the
    “Employeeaccrualpolicy” Table
    check to see if
    “EmployeeId”
    is assigned to
    EmployeeAccrualPolicyId = 21 or not.


    Below is the query to run to find out what employees are stopping this from happening (Thanks Nancee for your amazing help)


    select EmployeeCode from Employee where
    EmployeeId in
    (select distinct EmployeeId from EmployeeAccrualPolicy
    where AccrualPolicyId != 21 and
    EmployeeAccrualPolicy.EmployeeId in (select distinct employeeid from Absence
     where AbsencePolicyId = 31)
    and IsActive = 1)
    and EmployeeId Not in (select distinct EmployeeId from EmployeeAccrualPolicy where
    AccrualPolicyId = 21)
    order by EmployeeCode

    here is another one that seemed to work also that was simplified

    select EmployeeCode from Employee where
    EmployeeId in 
    (select distinct employeeid from Absence where AbsencePolicyId = 31)
    and EmployeeId Not in 
    (select distinct EmployeeId from EmployeeAccrualPolicy where AccrualPolicyId = 21)
      order by EmployeeCode

    Wednesday, October 30, 2013 11:19 PM