Query Problem

cprash.aggarwal · #1 (**permalink**) 03-15-09, 14:12

I have 3 tables

X

OID PN

1 A
2 B
3 C

Y

OID EC

2 D
4 E
6 F

Z

OID_X OID_Y

1 2
3 4
3 6
5 2
5 6

OID_X REFERS TO OID IN TABLE X AND OID_Y REFERS TO OID IN TABLE Y

nOW, I WANT RESULT AS
PN EC

A D
B E
B F
C D
C F
WHICH IS SAME AS COMBINATION IN TABLE Z,

i TRIED THESE BUT NONE IS GIVING DESIRED RESULTS.

SELECT PN, EC FROM X, Y, Z WHERE Z.OID_X=X.OID AND Z.OID_Y=Y.OID

SELECT PN, EC FROM X, Y, Z WHERE (X.OID,Y.OID) IN (SELECT OID_X, OID_Y FROM Z)

PLEASE HELP

THANKS

r937 · #2 (**permalink**) 03-15-09, 15:39

you have over-simplified your problem to the point where i believe that even you have lost track of the Xs and Ys

kindly rephrase your question

tonkuma · #3 (**permalink**) 03-15-09, 22:54

The result
PN EC

A D
B E
B F
C D
C F

would be obtained by

Code:

------------------------------ Commands Entered ------------------------------
WITH X(OID, PN) AS (
VALUES
 (1, 'A')
,(2, 'B')
,(3, 'C')
)
,Y(OID, EC) AS (
VALUES
 (2, 'D')
,(4, 'E')
,(6, 'F')
)
,Z(OID_X, OID_Y) AS (
VALUES
 (1, 2)
,(3, 4)
,(3, 6)
,(5, 2)
,(5, 6)
)
SELECT pn, ec
  FROM x, y, z
 WHERE oid_x = x.oid * 2 - 1
   AND oid_y = y.oid
;
------------------------------------------------------------------------------

PN EC
-- --
A  D 
B  E 
B  F 
C  D 
C  F 

  5 record(s) selected.

But, I am not sure that is what you want.

Internet Services

Web Development

Digital Marketing

Consumer Tech