how to combine two different result into one

extremebliss · #1 (**permalink**) 06-29-11, 23:49

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

sidukas · #2 (**permalink**) 06-30-11, 03:03

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'

extremebliss · #3 (**permalink**) 06-30-11, 03:33

Quote:

Originally Posted by sidukas

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

shammat · #4 (**permalink**) 06-30-11, 04:47

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)

sidukas · #5 (**permalink**) 06-30-11, 06:10

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech