[SQL] 여러 테이블에서 SQL SELECT

여러 테이블에서 SQL SELECT

어떻게 customers1에서 모든 제품을 얻을 수 있으며, 자신의 고객의 이름을 포함 customers2?

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

결과는 다음과 같이해야한다

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

해결법

1. ==============================

   1.

   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
   

2. ==============================

   2.

   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;
   

3. ==============================

   3.

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

4. ==============================

   4.

   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
   

5. ==============================

   5.

   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
   

6. ==============================

   6.

   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
   

7. ==============================

   7.나는 일부는 7 개 테이블에서 다음과 같이 합류 생각

   나는 일부는 7 개 테이블에서 다음과 같이 합류 생각

   SELECT a.no_surat , 
   a.nm_anggota , 
   a.nrp_nip_anggota , 
   a.tmpt_lahir , 
   a.tgl_lahir , 
   a.bln_lahir , 
   a.thn_lahir , 
   a.alamat , 
   a.keperluan , 
   a.nm_jabatan , 
   b.id_polsek ,b.nm_polsek, 
   c.id_polres ,c.nm_polres , 
   d.id_pangkat , d.nm_pangkat, 
   e.id_pejabat , e.nm_pejabat , 
   f.id_ket , f.nm_ket, 
   g.id_pejabat,g.nm_pejabat 
   FROM tbl_skhp AS a 
   LEFT JOIN tbl_polsek AS b ON a.id_polsek=b.id_polsek 
   LEFT JOIN tbl_polres AS c ON a.id_polres=c.id_polres 
   LEFT JOIN tbl_pangkat AS d ON a.id_pangkat=d.id_pangkat
   LEFT JOIN tbl_pejabat AS e ON a.id_pejabat=e.id_pejabat
   LEFT JOIN tbl_ket AS f ON a.id_ket=f.id_ket 
   LEFT JOIN tbl_pejabat AS g ON a.id_pejabat=g.id_pejabat
   

   난 .... u는 이해 바랍니다 난 그냥 나를 위해 일한 코드를 공유하고 .... 난 그냥 priview 내 읽기 전용 형태로 데이터를 가져 오기 위해 사용입니다 ...

from https://stackoverflow.com/questions/1416003/sql-select-from-multiple-tables by cc-by-sa and MIT license