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.

Perico de los Palotes wrote:


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
__________________________________________________ ____

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

Thanks,

Perico


"___cliff rayman___" <cliff (AT) SPAMMER_GO_HOME_rayman (DOT) com> wrote

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:

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 (AT) SPAMMER_GO_HOME_rayman (DOT) com> wrote

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:

--
_____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
__________________________________________________ ____