Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: Retrieving results without cross joining

    Hello everyone.

    I wanted to check if anyone could help me on solving a situation that has been in my mind for the last week. What I'm trying to do is to retrieve a set of rows from multiple tables, but without having the implications of repeating because of cross joining.

    For example, suppose we have the following tables
    Customer: id, name, date_of_birth, gender
    Customer_Phone: id, id_customer, id_phone_type, phone_number
    Customer_Phone_Type: id, description
    Orders: id, customer_id, requested_date, status, amount

    And we had the following information
    Customer (id/name/dob/gender):
    1/john smith/1979-01-01/m

    Customer_Phone_Type (id/description):
    1/mobile
    2/office

    Customer_Phone (id/id_customer/id_phone_type, phone_number):
    1/1/1/555-5555555
    2/1/2/999-9999999

    And ten orders stored in the Orders table with customer_id = 1

    How could I retrieve the selection of customer name, phone numbers and orders without cross joining (because i typically would get 20 orders based on this scenario because i have two phone numbers)? So what i would be looking for would be something like this

    Customer Name Phone Type Phone Number Order ID Order Date
    John Smith Mobile 555-5555555 1 2008-11-01
    Office 999-9999999 2 2008-11-02
    3 2008-11-03
    4 2008-11-04
    5 2008-11-05
    6 2008-11-06
    7 2008-11-07
    8 2008-11-08
    9 2008-11-09
    10 2008-11-10


    I've been trying with different alternatives but haven't been able to reproduce it to get those results

    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    use a pivot query with your data.
    granted, you would need to know how many phone numbers they had
    ahead of time.

    look up pivot query on asktom. he also has a dynamic solution.
    I tested it out a while back and the genius was right. it worked.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Are there any business rules limiting the number of phone_number types people can have? Like 1 mobile & 1 office number? If not, you're going to get into a confusing situation (like which of the 2 mobile numbers is the right one ... etc).

    But once you get that figured out (maybe like, you can have multiple numbers of a particular type, but only one can also be labeled "primary") then you can go with something like:

    Code:
    select c.customer_info, p.phone_number
    from customer c, customer_phone p
    where c.id = p.id_customer and
          p.type = (select min(p2.type) 
                    from   customer_phone p2 
                    where p.id_customer = p2.id_customer)
    Just make the "id" in the cutomer_phone_type ascend in order of which phone type you would prefer to see first, if they have that type (in the above SQL, it's saying show me their mobile if they have it, otherwise show me their office #).

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink wm_concat()

    Use the WM_CONCAT() function:
    Code:
    SQL> Drop Table Cust;
    
    Table dropped.
    
    SQL> Create Table Cust (Id Number, Name Varchar2(20),Dob Date, Gender Char(1));
    
    Table created.
    
    SQL> Insert Into Cust Values (1,'John Smith',To_Date('1979-01-01','Yyyy-Mm-Dd'),'M');
    
    1 row created.
    
    SQL> Drop Table Cust_Phone_Type;
    
    Table dropped.
    
    SQL> Create Table Cust_Phone_Type (Id Number,Description Varchar2(10));
    
    Table created.
    
    SQL> Insert Into Cust_Phone_Type Values(1,'Mobile');
    
    1 row created.
    
    SQL> Insert Into Cust_Phone_Type Values(2,'Office');
    
    1 row created.
    
    SQL> Drop Table Cust_Phone;
    
    Table dropped.
    
    SQL> Create Table Cust_Phone (Id Number,Cust_Id Number, Phone_Type Number, Phone_Number Varchar2(20));
    
    Table created.
    
    SQL> Insert Into Cust_Phone Values (1,1,1,'555-5555555');
    
    1 row created.
    
    SQL> Insert Into Cust_Phone Values (2,1,2,'999-9999999');
    
    1 row created.
    
    SQL> Drop Table Orders;
    
    Table dropped.
    
    SQL> Create Table Orders (Ord_Num Number, Cust_Id Number, Ord_Dt Date);
    
    Table created.
    
    SQL> Insert Into Orders Values (01,1,To_Date('2008-11-01','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Insert Into Orders Values (02,1,To_Date('2008-11-02','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Insert Into Orders Values (03,1,To_Date('2008-11-03','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Insert Into Orders Values (04,1,To_Date('2008-11-04','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Insert Into Orders Values (05,1,To_Date('2008-11-05','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Insert Into Orders Values (06,1,To_Date('2008-11-06','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Insert Into Orders Values (07,1,To_Date('2008-11-07','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Insert Into Orders Values (08,1,To_Date('2008-11-08','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Insert Into Orders Values (09,1,To_Date('2008-11-09','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Insert Into Orders Values (10,1,To_Date('2008-11-10','Yyyy-Mm-Dd'));
    
    1 row created.
    
    SQL> Commit;
    
    Commit complete.
    
    SQL> col name          for a16
    SQL> col phone_numbers for a40 wor
    SQL> col ord_num          for 99999
    SQL> break on name nodup on phone_numbers nodup on report
    SQL> with cust_data as (
      2  select c.id, c.name
      3       , wm_concat(' '||t.description||': '||p.phone_number) Phone_Numbers
      4    from cust c, cust_phone p, cust_phone_type t
      5   where p.cust_id = c.id and t.id = p.phone_type
      6   group by c.id, c.name )
      7  select d.name, d.phone_numbers, o.ord_num, o.ord_dt
      8    from cust_data d, orders o
      9   where o.cust_id = d.id
     10   order by d.name, d.phone_numbers, ord_num
     11  /
    
    NAME             PHONE_NUMBERS                            ORD_NUM ORD_DT
    ---------------- ---------------------------------------- ------- ---------
    John Smith       Mobile: 555-5555555, Office: 999-9999999       1 01-NOV-08
                                                                    2 02-NOV-08
                                                                    3 03-NOV-08
                                                                    4 04-NOV-08
                                                                    5 05-NOV-08
                                                                    6 06-NOV-08
                                                                    7 07-NOV-08
                                                                    8 08-NOV-08
                                                                    9 09-NOV-08
                                                                   10 10-NOV-08
    
    10 rows selected.
    
    SQL>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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