Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: how to combine two different result into one

    TABLES
    customer: customer_id, customer_name, customer_phone
    customeraddress: customeraddress_id, customer_id, address_id
    address: address_id, address_type, street_name, city

    how to get two addresses for one customer to put in one result

    hope you guys/gals can help
    query:
    select c.customer_id, c.customer_name, ca.address_id, a.address_type, a.city, a.street_name from customer c, customeraddress ca, address a
    where a.address_type ="workaddress" and ca.address_id = a.address_id
    and ca.customer_id = c.customer_id

    and

    select c.customer_id, c.customer_name, ca.address_id, a.address_type, a.city, a.street_name from customer c, customeraddress ca, address a
    where a.address_type ="homeaddress" and ca.address_id = a.address_id
    and ca.customer_id = c.customer_id

    the result is customer_name, homeaddress, workaddress, phonenumber
    Last edited by extremebliss; 06-30-11 at 01:40. Reason: revised the sentence

  2. #2
    Join Date
    Jun 2011
    Posts
    11
    something like that. can be syntax errors here

    Code:
    select 
    c.customer_name, a.address_type, a.city, a.street_name, aa.address_type, aa.city, aa.street_name, c.phonenumber
    from customer c
    JOIN customeraddress ca
      ON ca.address_id = a.address_id
    JOIN  address a
      ON ca.address_id = a.address_id
    JOIN  address aa
      ON ca.address_id = aa.address_id
    where a.address_type ='workaddress' 
         AND aa.address_type ='homeaddress'

  3. #3
    Join Date
    Jun 2011
    Posts
    2
    Quote Originally Posted by sidukas View Post
    something like that. can be syntax errors here

    Code:
    select 
    c.customer_name, a.address_type, a.city, a.street_name, aa.address_type, aa.city, aa.street_name, c.phonenumber
    from customer c
    JOIN customeraddress ca
      ON ca.address_id = a.address_id
    JOIN  address a
      ON ca.address_id = a.address_id
    JOIN  address aa
      ON ca.address_id = aa.address_id
    where a.address_type ='workaddress' 
         AND aa.address_type ='homeaddress'

    i tried your sql query and no results was found. below is a sample data and the table i wish to accomplish.

    customer
    customer_id || customer_name || customer_phone
    1 || robert || 123456

    customeraddress
    customer_address_id || customer_id || address_id
    001 || 1 || 101
    002 || 1 || 102

    address
    address_id || address_type || city
    101 || homeaddress || new york
    102 || workaddress || los angeles

    output
    customer_id || customer_name || workaddress || homeaddress
    1 || robert || los angeles || new york

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Try this
    Code:
    SELECT c.customer_id,
           c.customer_name, 
           wa.city AS workaddress,
           ha.city AS homeaddress
    FROM customer c
      JOIN customeraddress cwa
        ON cwa.customer_id = c.customer_id
      JOIN address wa
        ON cwa.address_id = wa.address_id AND wa.address_type = 'workaddress' 
      JOIN customeraddress hwa
        ON hwa.customer_id = c.customer_id
      JOIN address ha
        ON hwa.address_id = ha.address_id AND ha.address_type = 'homeaddress'
    An alternative would be this one:
    Code:
    WITH workaddress (city, customer_id) AS (
      SELECT a.city, ca.customer_id  
      FROM address a
       JOIN customeraddress ca 
         ON a.address_id = ca.address_id AND a.address_type = 'homeaddress'
    ),
    homeaddress (city, customer_id) AS (
      SELECT a.city, ca.customer_id  
      FROM address a
       JOIN customeraddress ca 
         ON a.address_id = ca.address_id AND a.address_type = 'workaddress'
    )
    SELECT c.customer_id,
           c.customer_name, 
           ha.city AS homeaddress,
           wa.city AS workaddress
    FROM customer c
      JOIN workaddress wa ON wa.customer_id = c.customer_id
      JOIN homeaddress ha ON ha.customer_id = c.customer_id
    ;
    But that is probably slower than my first solution (because the two selects on customeraddress inside the CTE are not limited to the customer_id)

  5. #5
    Join Date
    Jun 2011
    Posts
    11
    you can also do something like:

    Code:
    create table customer (customer_id integer, customer_name varchar(100), customer_phone varchar(100) );
    create table customer_address (customer_address_id integer, customer_id integer, address_id integer);
    create table address (address_id integer, address_type varchar(100), city varchar(100));
    
    INSERT INTO customer values(1,'robert',123456);
    INSERT INTO customer_address values(1,1,101);
    INSERT INTO customer_address values(2,1,102);
    INSERT INTO address values(101,'homeaddress','new york');
    INSERT INTO address values(102,'workaddress','los angeles');
    
    select c.customer_id, c.customer_name, max(a.city) as workaddress, min(a.city) as homeaddress
    FROM customer c
    JOIN customer_address ca
      ON c.customer_id = ca.customer_id
    JOIN address a
      ON a.address_id = ca.address_id
     GROUP BY 1,2;

Posting Permissions

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