Re: SELECT question once again.
Date: 17 Mar 2000 16:53:58 GMT
Message-ID: <20000317115358.02496.00002663_at_ng-ca1.aol.com>
>TONY WROTE:
>I am sorry to confuse you.
>Forget Oracle demo EMP, DEPT
>
>Lets have table DEPT with columns DEPT_ID, DEPT
>and a few values
>DEPT1_ID, DEPT1
>DEPT2_ID, DEPT2
>...
>
>Lets have table EMP with columns EMP_ID, EMP, DEPT_ID
>and a few values
>EMP1_ID, EMP1, DEPT1_ID
>EMP2_ID, EMP2, DEPT1_ID
>EMP3_ID, EMP3, DEPT1_ID
>EMP4_ID, EMP4, DEPT2_ID
>EMP5_ID, EMP5, DEPT2_ID
>...
>
>Can you help me now?
>
>Regards Tony.
>
>Sanrenkur wrote:
>
>> >TONY WROTE:
>>
>> >My previous question was not answered so I'll try to
>> >make it more clear. The best way to see the problem is,
>> >to look at this message with "fix pitch" font, like Courier.
>> >
>> >I have following problem:
>> >Table DEPT columns DEPT_ID, DEPT_NAME
>> >Table EMP, columns EMP_ID, EMP_NAME, DEPT_ID
>> >
>> >I need to build a LOV listing employees ordered by department.
>> >I can easily build a view (record group) giving:
>> >DEPT1 EMP1 DEPT1_ID EMP1_ID
>> >DEPT1 EMP2 DEPT1_ID EMP2_ID
>> >DEPT1 EMP3 DEPT1_ID EMP3_ID
>> >DEPT2 EMP4 DEPT2_ID EMP4_ID
>> >DEPT2 EMP5 DEPT2_ID EMP5_ID
>> >
>> >What I want instead is:
>> >DEPT1 EMP1 DEPT1_ID EMP1_ID
>> > EMP2 DEPT1_ID EMP2_ID
>> > EMP3 DEPT1_ID EMP3_ID
>> >DEPT2 EMP4 DEPT2_ID EMP4_ID
>> > EMP5 DEPT2_ID EMP5_ID
>> >
>> >Is there anyone who can give me a hint how to build a view like this
>> >or any reference, where I can find similar solution?
>> >
>> >Thanks Tony.
>> >
>>Hi Tony,
But those aren't the actual values, are they?
In other words;
DEPT_ID DEPT 10 Marketing 20 Sales
Something more on those lines....
Your query:
SELECT dept_id, emp_id
FROM emp
GROUP BY dept_id;
Is that what you are looking for?
or
SELECT d.dept_id, e.emp_id,e.emp_name
d.dept
FROM emp e, dept d
WHERE e.dept_id = d.dept_id
GROUP BY d.dept_id,e.emp_id,e.emp_name,d.dept;
or this?
HTH sandy
Received on Fri Mar 17 2000 - 17:53:58 CET