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!

join two tables to get the customized data

Discussion in 'SQL PL/SQL' started by ketangarg86, Apr 16, 2015.

  1. ketangarg86

    ketangarg86 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I am relatively new to Oracle SQL and have a small project. I need to do the below:
    1. Get the distinct email address that are sending orders
    2. Sender name
    3. Vendor name
    4. Count of orders by that email address
    5. Whether the email address is from the preferred vendor (I have a predefined list, Apple and RBS are the preferred vendors in this case).

    I would really appreciate if you could please help me out. Below are the sample tables.

    Table 1
    Email Id Sender Orders Source Id
    123@APPLE.COM Jon 10 1
    345@YAHOO.COM Ron 15 2
    123@APPLE.COM Jon 10 1
    234@IBM.COM Pete 15 3
    234@GOOGLE.COM Peter 10 4
    345@GOOGLE.COM Aaron 15 4
    111@FACEBOOK.COMMike 25 5
    222@TCS.COM Michael 20 6
    665@RBS.COM Ming 11 7
    345@UBS.COM Ping 15 8
    567@UBS.COM Sing 60 8

    Table 2
    ID Source Name
    1 Apple
    2 Yahoo
    3 IBM
    4 Google
    5 Facebook
    6 TCS
    7 RBS
    8 UBS

    Result
    Email id Sender Source Name Count Orders Is In list
    123@apple.com Jon Apple 20 Yes
    345@YAHOO.COM Ron Yahoo 15 No
    234@GOOGLE.COM Peter Google 10 No
    and so on
     
  2. vprog

    vprog Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Ghaziabad
    The query can be simplified in all these queries....
    1) SELECT Email Id FROM TABLE 1;
    2) SELECT Sender FROM TABLE 1;
    3) SELECT Source Name FROM TABLE 2;
    4) SELECT Orders FROM TABLE 1;
    5) SELECT Email Id FROM TABLE 1 WHERE Source Id = 1 OR Source Id = 7;

    OR

    The query can be combined into one query....

    SELECT Email Id, Sender, Source Name, Orders, Is In list FROM Table 1, Table 2 WHERE Table 1.Source Id = Table 2.ID;
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hope the join between the 2 tables is the Source_id...


    Code (SQL):


    CREATE TABLE TABLE_1
    (
    EMAIL_ID varchar2(50), 
    SENDER  varchar2(50),
    ORDERS  varchar2(50),
    SOURCE_ID varchar2(50)
    );

    &

    CREATE TABLE TABLE_2
    (
    vendor_id varchar2(50),
    vendor_name varchar2(50)
    );

     
    And the Qry, U can use is...

    Code (SQL):


    SELECT EMAIL_ID,SENDER,VENDOR_NAME,SUM(ORDERS)
    FROM TABLE_1,TABLE_2
    WHERE VENDOR_ID=SOURCE_ID --and EMAIL_ID in ('UR Preferred List')
    GROUP BY EMAIL_ID,SENDER,VENDOR_NAME;

     
    And, I've no Idea about, from where U're trying to fetch this column..'Orders Is In list'
     
  4. sowmya

    sowmya Guest

    Hi,

    Could you tell me if the below query is correct or wrong.

    select distinct(t1.emailid), t1.sender, t2.sourcename, count(*) from table1 t1
    join table2 t2
    on t1.sourceid = t2.id
    where t2.sourcename in('Apple','RBS')
    group by t1.emailid, t1.sender, t2.sourcename;

    Thank you
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    hi.

    your query isn't correct.

    pay attention : distinct(t1.emailid) . in the oracle there is no distinct function(distinct as function is officially not documented).

    you will formulate, what result shall return your query?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I beg to differ:


    Code (SQL):

    SQL> SELECT DISTINCT deptno FROM emp;


        DEPTNO
    ----------
            30
            20
            10


    SQL>
     
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    David:

    I believe that the distinction Sergey is making about DISTINCT is that it is not a function, not that the capability doesn't exist. He is correct in that -- DISTINCT is a SQL keyword, not a function. However, he is incorrect in believing that enclosing the column(s) to be acted on by the DISTINCT keyword in parenthesis will not work:

    Code (Text):
    SELECT DISTINCT department_id
    FROM   hr.employees
    WHERE  employee_id < 107;

    DEPARTMENT_ID
    -------------
               90
               60
               

    SELECT DISTINCT(department_id)
    FROM   hr.employees
    WHERE  employee_id < 107;

    DEPARTMENT_ID
    -------------
               90
               60
    I wasn't really sure how it would work with other columns and aggregation like the poster he was replying to had, but even that seems to work. At least it executes -- the results are a little screwy:

    Code (Text):
    SELECT DISTINCT(department_id), job_id, SUM(salary)
    FROM   hr.employees
    GROUP BY department_id, job_id;

    DEPARTMENT_ID JOB_ID     SUM(SALARY)
    ------------- ---------- -----------
              110 AC_ACCOUNT        8300
               30 IT_PROG                
               90 AD_VP            34000
               50 ST_CLERK         55700
               80 SA_REP          243500
               50 ST_MAN           36400
               80 SA_MAN           61000
              110 AC_MGR           12008
               90 AD_PRES          24000
               60 AD_VP             9000
               60 IT_PROG          19800
              100 FI_MGR           13000
               30 PU_CLERK         13900
               50 SH_CLERK         64300
               20 MK_MAN           13000
              100 FI_ACCOUNT       39600
                  SA_REP            7000
               70 PR_REP           10000
               30 PU_MAN           11000
               10 AD_ASST           4400
               20 MK_REP            6000
               40 HR_REP            6500