1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help with SQL Query

Discussion in 'SQL PL/SQL' started by Shekhar81, Nov 25, 2008.

  1. Shekhar81

    Shekhar81 Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    I am trying to do something and needed some help. Am giving an example of what I want.

    Say, I have three tables
    Code (Text):

    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:
    Code (Text):

    EMP_NAME PRODUCT_NAME

    EMP1 PRODUCT1
    EMP2 PRODUCT2
    PRODUCT3
     
    Thanks for your help folks, really appreciate it.
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Not really sure what you want, but try this (not tested)
    Code (Text):

    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 )
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    He the problem is if we join employee and product , we will get 6 rows while the OP needs only 3 rows .

    So we need to have One additional field that is needed for proper join. We may have to rely on analytic function with FULL OUTER JOIN .

    Following is One solution ( Not tested )

    Code (Text):
    SELECT NVL(e.LOCATION_NO,p.LOCATION_NO) LOCATION_NO,
           e.EMP_NAME,
           d.PRODUCT_NAME
    FROM   (SELECT LOCATION_NO,EMP_NAME,
                   ROW_NUMBER() OVER (PARTITION BY LOCATION_NO ORDER BY  EMP_NAME ) rnm
            FROM EMPLOYEE ) e FULL OUTER JOIN
           (SELECT LOCATION_NO, PRODUCT_NAME,
                   ROW_NUMBER() OVER (PARTITION BY LOCATION_NO ORDER BY  PRODUCT_NAME ) rnm
            FROM PRODUCT) p
    WHERE e.LOCATION_NO = p.LOCATION_NO
    AND   e.rnm         = p.rnm
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or even you can join Location along with other two tables as below.

    Code (Text):

    SELECT NVL(e.LOCATION_NO,p.LOCATION_NO) LOCATION_NO,
           e.EMP_NAME,
           d.PRODUCT_NAME
    FROM   (SELECT LOCATION_NO,EMP_NAME,
                   ROW_NUMBER() OVER (PARTITION BY LOCATION_NO ORDER BY  EMP_NAME ) rnm
            FROM EMPLOYEE ) e FULL OUTER JOIN
           (SELECT LOCATION_NO, PRODUCT_NAME,
                   ROW_NUMBER() OVER (PARTITION BY LOCATION_NO ORDER BY  PRODUCT_NAME ) rnm
            FROM PRODUCT) p
    WHERE e.LOCATION_NO = p.LOCATION_NO
    AND   e.rnm         = p.rnm
    AND EXISTS ( SELECT NULL
             FROM   LOOCATION L
             WHERE  L.LOCATION_NO = NVL(e.LOCATION_NO,p.LOCATION_NO)
               )