Post reply

Simple Query

  • February 23, 2011 at 8:13 am

    #388920

    Quite new to SQL. Have two tables:

    Table 1 is employees: (Column Name), (Data Type), (Allow Nulls)

    ID, int, no nulls

    Empname, varchar(50), no nulls

    team, varchar(10), nulls

    branch, varchar(5), no nulls

    position, varchar(2), nulls

    Table 2 is RAWH: (Column Name), (Data Type), (Allow Nulls)

    ID, int, no nulls

    EmpPIN, varchar(6), nulls

    Empname, varchar(50), no nulls

    dateRequested, datetime, nulls

    startdate, datetime, nulls

    stopdate, datetime, nulls

    TypeHrs, varchar(50), nulls

    NoOfHours, decimal(4,2), nulls

    projects, varchar(200), nulls

    justification, varchar(200), nulls

    RCbeginDate, datetime, nulls

    RCendDate, datetime, nulls

    RCLvSlip, varchar(3), nulls

    reqDecision, varchar(8), nulls

    denialReason, varchar(300), nulls

    From the RAWH table I have the Empname generated into a text field. From this field containing the Empname I want to get from the employee table the following information which I will place into hidden input fields:

    team and branch.

    Now armed with team and branch I need to create another query to get a name from the employees' table from position where the teams are the same and the position shows tl (position has three entries - 1 is tl, 2 is bc and 3 is NULL).

    I also need to get the Empname from employees where position is bc and the braches are equal.

    I hope this makes sense.

    Any help is greatly appreciated.

    Thank you - JS

  • February 23, 2011 at 1:08 pm

    #1290600

    This query should do what you are looking for (i think...i'm not entirely sure what you are looking for):

    (I also don't see why you need the RAWH table, but i put it in the SQL

    From the RAWH table I have the Empname generated into a text field. From this field containing the Empname I want to get from the employee table the following information which I will place into hidden input fields:

    team and branch.

    Now armed with team and branch I need to create another query to get a name from the employees' table from position where the teams are the same and the position shows tl (position has three entries - 1 is tl, 2 is bc and 3 is NULL).

    I also need to get the Empname from employees where position is bc and the braches are equal.

    SELECT RAWH.EmpName, e.team, e.branch, e2.empname as TeamMemberName, e3.empname as BranchMemberName

    from employee e

    join RAWH on e.empname = RAWH.empname

    left join employee e2 on e.team = e2.team and e2.position = 'tl'

    left join employee e3 on e.branch = e3.branch and e3.position = 'bc'

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

  • February 24, 2011 at 9:47 am

    #1291081

    Many, many thanks ChazMan. Your information is appreciated

    JS

  • February 27, 2011 at 4:01 pm

    #1292083

    As a bit of a sidebar... what happens when one of your team members changes her name due to marriage? What happens when you have two people employed with the same name?

    My recommendation is to use some form of EmployeeID (or number or whatever you want to call it) instead of a freeform key such as Name.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 28, 2011 at 5:00 am

    #1292235

    Thank you. Using a PIN (Personal Id Number) as a primary key.

  • February 28, 2011 at 5:01 am

    #1292236

    Thank you ChazMan - very effective.

  • February 28, 2011 at 5:53 am

    #1292249

    I'm glad it helped!

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply