Simple query for 2 tables 1

[JohnShell]

Quite new to SQL and no idea how to do this. 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

 

[gmmastros]

I think your question would be a lot clearer if you posted some sample data from the 2 tables involved and then showed your expected results (the data you hope to get) from the query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnShell]

Bear with me and my limited knowledge

Employees table sample data:

ID Empname Team Branch Position email
3 Rick Jones NULL VEB bc RJones@
4 Darlene Washington EET VEB tl DWash@
6 Andrea Winters EET VEB NULL AWinters@
7 Debbie Duncan EET VEB NULL DDuncan@
22 Ed Robinson T2VT VMB tl ERobby@
23 Steve Shilling T2VT VMB NULL SShilling@
24 Joe Carpenter T2VT VMB NULL JCarpenter@
35 Brian Laurence NULL VUB bc BLaurence@
36 Jessica Schmittle T2VDB VUB tl JSchmittle@
37 Joe Arquette T2VDB VUB NULL JArquette@
38 Eric Redman T2VDB VUB NULL ERedman@

RAWH table sample data:

ID EmpPIN Empname dateRequested startdate enddate
224 123456 Jon Smith 1/13/2011 1/19/2011 1/19/2011
225 654321 Bob Jones 1/13/2011 1/27/2011 1/27/2011
226 234567 Sal Tripp 1/13/2011 1/17/2011 1/17/2011
227 345678 Val Hopps 1/13/2011 1/18/2011 1/18/2011
228 456789 Jim Stone 1/13/2011 1/19/2011 1/19/2011
229 567891 Dom Kamp 1/13/2011 1/20/2011 1/20/2011
230 678912 Lee King 1/13/2011 1/21/2011 1/21/2011
231 789123 Rob Ermy 1/13/2011 1/24/2011 1/24/2011
232 891234 Phil Watts 1/18/2011 1/19/2011 1/19/2011
233 912345 Jane Payne 1/18/2011 1/24/2011 1/27/2011

This second table contains more columns but nothing relating to the first table. The only field that relates is that of Empname.

There is a form for the user. The user's EmpPIN and Empname is captured via code from Core Services. The user completes the form and that data is inserted into the RAWH table. The form and data is actually a request to a supervisor. The supervisor is called a team lead. An email is sent to the user's team lead, and a cc to the team leader's supervisor who is a branch chief(bc), notifying him/her of a request. The email has a link to another web page where the team lead approves or denies the request.
The data I need to get is for the email - I need the user's team lead and branch chief which will have the their respective email addresses. I need to direct the user's request to the correct team lead and branch chief. On the second web page - the team lead approval page I need to send the approval or denial notification to the correct requester.
Does this make sense?
Thanks - JS

 

[SQLBill]

So ID 233 (Jane Payne) just got entered into RAWH. Using JUST the RAWH table, how do we know who Jane's supervisor (team lead) is?


-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[JohnShell]

Folks,

Figured it out and did this:

<cfquery datasource="RAWH" name="employee">
SELECT
name,
team,
branch,
position,
email,
(
SELECT E1.email
FROM employees AS E1
WHERE E1.team = employees.team
AND E1.position = 'tl'
) AS TL_EMAIL,
(
SELECT E3.position
FROM employees AS E3
WHERE E3.team = employees.team
AND E3.position = 'tl'
) AS TL_POSITION,
(
SELECT E2.email
FROM employees AS E2
WHERE E2.branch = employees.branch
AND E2.position = 'bc'
) AS BC_EMAIL,
(
SELECT E4.position
FROM employees AS E4
WHERE E4.branch = employees.branch
AND E4.position = 'bc'
) AS BC_POSITION
FROM employees
WHERE PIN = '#Form.PIN#'
</cfquery>

Thanks again - JS