Results 1 to 3 of 3

Thread: Query Problem

  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: Query Problem

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •