Re: Need help with SQL query involving a recursive query
- From: "Georgie" <filmfind@xxxxxxxxxx>
- Date: Wed, 30 Nov 2005 14:12:14 GMT
Worked! Thanks
Yeah, you're assumptions were right, I should have said that.
Thanks for the time spent on this, it was obviously a good bit
"Chris2" <rainofsteel.NOTVALID@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:xKCdnU5nh7CQWhHenZ2dnUVZ_tOdnZ2d@xxxxxxxxxxxxxx
>
> "Georgie" <filmfind@xxxxxxxxxx> wrote in message
> news:b%1jf.83277$xd2.7629@xxxxxxxxxxxxxxxxxxxxxxxxx
>> Heres the 2 tables involved:
>> EMPLOYEE
>> Emp_Num
>> Emp_Name
>> Date_of_Birth
>> Salary
>> Bonus
>> Manager_Num
>>
>> SALES_REP
>> Rep_Code
>> Telephone
>> Mobile_Phone
>> Region
>>
>> The question, "for all representatives in the North Connaught
> region, list
>> their name, total salary (including bonus), mobile phone number,
>> address and manager name."
>>
>> How do I work this??
>> The Emp_Num of an employee's manager is in the manager number
> field, so I
>> have to query the same table again. I have to do it in one query.
> It's a
>> question on a college exam paper.
>>
>
> Georgie,
>
> Note: I am also making the *huge* assumption that Emp_Num =
> Rep_Code. The attributes have different names, and so must
> represent different things, and yet, I can't see a way that the
> tables can be JOINed without making this assumption.
>
> Note: I am also making another assumption that Manger_Num = Emp_Num.
>
> Please forgive the dates appended to the table names.
>
> CREATE TABLE EMPLOYEE_20051129_1
> (Emp_Num INTEGER
> ,Emp_Name TEXT(10)
> ,Date_of_Birth DATETIME
> ,Salary CURRENCY
> ,Bonus CURRENCY
> ,Manager_Num INTEGER
> ,CONSTRAINT pk_EMPLOYEE_20051129_1
> PRIMARY KEY (Emp_Num)
> )
>
> Manager_Num cannot contain a Null and have a foreign key to Emp_Num
> (Emp_Num is a primary key, and cannot have NULLs).
>
> A CHECK constraint should be added to insure the integrity of
> Manager_Num, but I've omitted that step.
>
>
> CREATE TABLE SALES_REP_20051129_1
> (Rep_Code INTEGER
> ,Telephone TEXT(15)
> ,Mobile_Phone TEXT(15)
> ,Email TEXT(255)
> ,Region TEXT(255)
> ,CONSTRAINT pk_SALES_REP_20051129_1
> PRIMARY KEY (Rep_Code)
> ,CONSTRAINT fk_SALES_REP_20051129_1_EMPLOYEE_20051129_1_Emp_Num
> FOREIGN KEY (Rep_Code)
> REFERENCES EMPLOYEE_20051129_1 (Emp_Num)
> )
>
>
> Sample Data, EMPLOYEE_20051129_1:
>
> 1, BIG BOSS, 01/01/1900, 1000000, 10000000, Null
> 2, Jay, 02/02/1950, 100000, 20000, 1
> 3, Dee, 02/02/1950, 90000, 15000, 1
> 4, Jeff, 02/02/1950, 80000, 10000, 3
> 5, Mike, 02/02/1950, 70000, 5000, 3
> 6, George, 02/02/1950, 20000, 100, 5
>
> Sample Data, SALES_REP_20051129_1:
>
> 3, 111-222-3333, 111-222-3334, dee@xxxxxxxxxxx, South Connaught
> 4, 111-333-4444, 111-333-4445, jeff@xxxxxxxxxxx, North Connaught
> 5, 111-444-5555, 111-444-5556, mike@xxxxxxxxxxx, North Connaught
>
>
> Query:
>
> SELECT E1.Emp_Name
> ,E1.Salary + E1.Bonus
> ,S1.Mobile_Phone
> ,S1.Email
> ,(SELECT E01.Emp_Name
> FROM EMPLOYEE_20051129_1 AS E01
> WHERE E01.Emp_Num = E1.Manager_Num)
> AS Manager
> FROM EMPLOYEE_20051129_1 AS E1
> INNER JOIN
> SALES_REP_20051129_1 AS S1
> ON E1.Emp_Num = S1.Rep_Code
> WHERE S1.Region = "North Connaught"
>
>
> Results:
>
> Jeff, $90,000.00, 111-333-4445, jeff@xxxxxxxxxxx, Dee
> Mike, $75,000.00, 111-444-5556, mike@xxxxxxxxxxx, Dee
>
> Based on the sample data I created above, the results are exactly
> what I would expect.
>
>
> Sincerely,
>
> Chris O.
>
> PS Although meant for an sqlserver newsgroup, the
> following link is still applicable for MS Access:
> http://www.aspfaq.com/etiquette.asp?id=5006, and
> is excellent when it comes to detailing how to
> provide the information that will best enable
> others to answer your questions.
>
>
.
- Follow-Ups:
- Prev by Date: Re: Need help trying to combine queries into 1 query
- Next by Date: Re: Date Criteria
- Previous by thread: Re: Need help trying to combine queries into 1 query
- Next by thread: Re: Need help with SQL query involving a recursive query
- Index(es):
Relevant Pages
|
