Oracle Databases - Query question

Author

Query question

Perico de los Palotes

2005-08-04, 9:26 am

I wonder if there is a query for the following:

I have three tables

LOCATION (
LOCATION_NO ) -- there is location 1

EMPLOYEE (
LOCATION_NO,
EMP_NAME ) -- There are two employees for location 1

PRODUCT (
LOCATION_NO,
PRODUCT_NAME ) -- There are three products for location 1

There is no relation between employees and products. Can I get the
following result:

EMP_NAME PRODUCT_NAME

EMP1 PRODUCT1
EMP2 PRODUCT2
PRODUCT3

Thanks in advance for any help.

___cliff rayman___

2005-08-05, 3:25 am

Perico de los Palotes wrote:

>I wonder if there is a query for the following:
>
>I have three tables
>
>LOCATION (
>LOCATION_NO ) -- there is location 1
>
>EMPLOYEE (
>LOCATION_NO,
>EMP_NAME ) -- There are two employees for location 1
>
>PRODUCT (
>LOCATION_NO,
>PRODUCT_NAME ) -- There are three products for location 1
>
>There is no relation between employees and products. Can I get the
>following result:
>
>
>EMP_NAME PRODUCT_NAME
>
>EMP1 PRODUCT1
>EMP2 PRODUCT2
> PRODUCT3
>
>
>
>
>
UNTESTED FROM THE TOP OF MY HEAD:

SELECT emp_name, product_name
FROM employee e, product p
WHERE e.location_no = p.location_no
AND e.location in ( SELECT location_no FROM location )

>
>
>
>
>

--
_____cliff_rayman___
____________________
______________
Business Consulting and Turnaround Management
[web] http://www.rayman.com/
[web] http://all-clear-turnaround-management.com/
[eml] cliff _at_ rayman.com
[phn] 888-736-3802 x701
[fax] 818-743-7404
____________________
____________________
______________

Perico de los Palotes

2005-08-08, 8:26 pm

No, that results in the cartesian product of both tables; 6 rows in this
case.

Thanks,

Perico

"___cliff rayman___" < cliff@SPAMMER_GO_HOM
E_rayman.com> wrote in message
news:cIBIe.7251$vf.2784@tornado.socal.rr.com...
> Perico de los Palotes wrote:
>
> UNTESTED FROM THE TOP OF MY HEAD:
>
> SELECT emp_name, product_name
> FROM employee e, product p
> WHERE e.location_no = p.location_no
> AND e.location in ( SELECT location_no FROM location )
>
>
> --
> _____cliff_rayman___
____________________
______________
> Business Consulting and Turnaround Management
> [web] http://www.rayman.com/
> [web] http://all-clear-turnaround-management.com/
> [eml] cliff _at_ rayman.com
> [phn] 888-736-3802 x701
> [fax] 818-743-7404
> ____________________
____________________
______________

___cliff rayman___

2005-08-10, 3:25 am

Ok - so what does this give you that you dont want?:

SELECT e.emp_name, p.product_name
FROM employee e, product p
WHERE e.location_no = p.location_no

C

Perico de los Palotes wrote:
[color=darkred]
>No, that results in the cartesian product of both tables; 6 rows in this
>case.
>
>Thanks,
>
>Perico
>
>
>"___cliff rayman___" < cliff@SPAMMER_GO_HOM
E_rayman.com> wrote in message
>news:cIBIe.7251$vf.2784@tornado.socal.rr.com...
>
>

Perico de los Palotes

2005-08-10, 11:26 am

Thanks for getting back to me, Cliff

I am trying to get the results in a tabular form, something like

E1 P1
E2 P2
P3

instead of

E1 P1
E1 P2
E1 P3
E2 P1
E2 P2
E2 P3

which is the result from your query. It is just a matter of presentation.

My guess is, I need some combination of subselects, but I cannot figure it
out. An outer join will not do, because there could be either more
products than employees or vice versa.

Regards,

"___cliff rayman___" < cliff@SPAMMER_GO_HOM
E_rayman.com> wrote in message
news:jNfKe.7868$vf.93@tornado.socal.rr.com...[color=darkred]
> Ok - so what does this give you that you dont want?:
>
> SELECT e.emp_name, p.product_name
> FROM employee e, product p
> WHERE e.location_no = p.location_no
>
> C
>
> Perico de los Palotes wrote:
>

___cliff rayman___

2005-08-11, 3:26 am

I doubt if you are going to get what you want with a regular query.
Perhaps Discoverer will do what you want.

Perico de los Palotes wrote:

>Thanks for getting back to me, Cliff
>
>I am trying to get the results in a tabular form, something like
>
>E1 P1
>E2 P2
> P3
>
>instead of
>
>E1 P1
>E1 P2
>E1 P3
>E2 P1
>E2 P2
>E2 P3
>
>which is the result from your query. It is just a matter of presentation.
>
>My guess is, I need some combination of subselects, but I cannot figure it
>out. An outer join will not do, because there could be either more
>products than employees or vice versa.
>
>Regards,
>
>
>
>"___cliff rayman___" < cliff@SPAMMER_GO_HOM
E_rayman.com> wrote in message
>news:jNfKe.7868$vf.93@tornado.socal.rr.com...
>
>
>
>
>
>
>

--
_____cliff_rayman___
____________________
______________
Business Consulting and Turnaround Management
[web] http://www.rayman.com/
[web] http://all-clear-turnaround-management.com/
[eml] cliff -at-- rayman.com
[phn] 888-736-3802 x701
[fax] 818-743-7404
____________________
____________________
______________

Sponsored Links