left - SQL SELECT de múltiples tablas

left join sql server 2008

Question

¿Cómo puedo obtener todos los productos de los clientes1 y los clientes2 incluyen sus nombres de clientes?

customer1 table
cid name1
1 john
2 joe
customer2 table
cid name2
p1 sandy
p2 linda
product table
pid cid pname
1 1 phone
2 2 pencil
3 p1 pen
4 p2 paper

El resultado debería ser así

pid cid pname name1 name2
1 1 phone john NULL
2 2 pencil joe NULL
3 p1 pen NULL sandy
4 p2 paper NULL linda

Answer 1

SELECT `product`.*, `customer1`.`name1`, `customer2`.`name2`
FROM `product`
LEFT JOIN `customer1` ON `product`.`cid` = `customer1`.`cid`
LEFT JOIN `customer2` ON `product`.`cid` = `customer2`.`cid`

Answer 2

SELECT p.pid, p.cid, p.pname, c1.name1, c2.name2
FROM product AS p
LEFT JOIN customer1 AS c1
ON p.cid = c1.cid
LEFT JOIN customer2 AS c2
ON p.cid = c2.cid

Answer 3

SELECT p.pid, p.cid, p.pname, c1.name1, c2.name2
FROM product p
LEFT JOIN customer1 c1 ON p.cid = c1.cid
LEFT JOIN customer2 c2 ON p.cid = c2.cid

Answer 4

SELECT pid, cid, pname, name1, name2
FROM customer1 c1, product p
WHERE p.cid=c1.cid
UNION SELECT pid, cid, pname, name1, name2
FROM customer2 c2, product p
WHERE p.cid=c2.cid;

Answer 5

select p.pid, p.cid, c1.name,c2.name
from product p
left outer join customer1 c1 on c1.cid=p.cid
left outer join customer2 c2 on c2.cid=p.cid

Answer 6

select pid, cid, pname, name1, null
from product p
inner join customer1 c on p.cid = c.cid
union
select pid, cid, pname, null, name2
from product p
inner join customer2 c on p.cid = c.cid