mysql 서브쿼리 관련 질문입니다 0 7 1,972

by 함박눈 [MySQL] mysql 서브쿼리 [2016.01.28 13:37:41]


모델에서 CUM_COMPANY라는 회원사테이블을 select 해옵니다. 
그런데 staff라는 회원테이블의 데이터도 불러와야 합니다. 
(staff 테이블에선 foreach를 돌려서 데이터를 컨트롤러에서 사용해야 하구요) 
문제는 join을 할 수가 없습니다ㅠ CUM_COMPANY테이블의 필드 228개만 가져와야하는데 join을 해버릴 경우 
필드 수가 맞지않습니다. 

일단 제가 left조인과 서브쿼리를 이용해서 쿼리를 날렸는데 원하는 결과가 출력되진 않았습니다.

회원사테이블의 228개 레코드가 출력되어야 하는데 총 194개의 레코드네요ㅠ 

CUM_COMPANY 회원사테이블을 기준으로 staff테이블의 데이터도 불러올 수 있는 방법 없을까요?ㅠ 
그리고 조건이 staff테이블의 cs_id와 cs_db가 같아야합니다 

아래는 제가 짠 쿼리입니다. 무엇이 문제인지 잘 모르겠습니다ㅠ;;

SELECT SQL_CALC_FOUND_ROWS * FROM cms_master_login.cum_company AS CC
 left join cms_master_login.master_login_staff as CL on CC.c_num = CL.c_num
 where CL.cs_id in (select cs_id from cms_master_login.master_login_staff where cs_id = cs_db)
 ORDER BY CC.reg_day DESC

 

by DeSSa [2016.01.28 14:59:27]

혹시 두 테이블의 스키마를 올려 주실 순 없을 런지요?
 show create table cms_master_login.cum_company ;
 show create table cms_master_login.master_login_staff;

 

아래와 같은 쿼리 수행 후,

 1. SELECT COUNT(CC.c_num)
 FROM CC
 INNER JOIN CL ON CC.c_num = CL.c_num AND CC.cs_id = CL.cs_id
 
 2. SELECT COUNT(CC.c_num)
 FROM CC
 LEFT JOIN CL ON CC.c_num = CL.c_num AND CC.cs_id = CL.cs_id

1, 2 쿼리에 대한 갯수를 비교 한 뒤 차이가 있다면 CC테이블과 CL테이블의 cs_id의 종류가 다른것이

존재 하다고 볼 수 있습니다.  이후 두 쿼리문에 WHERE 절에 WHERE CL.cs_id = CL.cs_db 넣어 보고

갯수를 확인 해 보면 어느정도 유추가 가능 할 듯 합니다.

ex) CC테이블에는 cs_id값과 일치 하는 CL.cs_id 중 cs_db가 일치 하지 않을 경우 갯수 차이가 있을 수 있습니다.


by 함박눈 [2016.01.28 15:30:44]

cum_company 테이블생성 쿼리입니다

CREATE TABLE `cum_company` (
  `c_num` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `reg_day` datetime NOT NULL COMMENT '생성일자',
  `mod_day` datetime NOT NULL COMMENT '수정일자',
  `c_state` tinyint(2) unsigned NOT NULL COMMENT '회원상태:1=정상회원,2=일시정지회원,3=삭제회원',
  `ks_num_last` varchar(10) COLLATE utf8_bin NOT NULL COMMENT 'hi_kostat_kssc.ks_num(유형변경소지있음,코드에 앞자리 0이 포함됨)',
  `ks_num_full` varchar(150) COLLATE utf8_bin NOT NULL,
  `c_com_name` varchar(250) COLLATE utf8_bin NOT NULL COMMENT '회사명',
  `c_ceo_name` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '대표자명',
  `c_com_number` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '사업자번호',
  `c_com_phone` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '회사연락처',
  `c_homepage` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '홈페이지',
  `c_com_fax` varchar(100) COLLATE utf8_bin NOT NULL COMMENT 'fax번호',
  `c_com_zip` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '회사우편번호',
  `c_com_addr1` varchar(250) COLLATE utf8_bin NOT NULL COMMENT '주소1',
  `c_com_addr2` varchar(250) COLLATE utf8_bin NOT NULL COMMENT '주소2',
  `c_loge_file_name` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '로고이미지명(경로)',
  `count_substance` int(15) NOT NULL DEFAULT '0' COMMENT '단일물질 수 ',
  `count_mixture` int(15) NOT NULL DEFAULT '0' COMMENT '혼합물 수 ',
  `count_product` int(15) NOT NULL DEFAULT '0' COMMENT '제품 수 ',
  PRIMARY KEY (`c_num`),
  KEY `c_state` (`c_state`)
) ENGINE=MyISAM AUTO_INCREMENT=249 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='회원사_정보'




CREATE TABLE `master_login_staff` (
 `cs_num` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `c_num` int(11) unsigned NOT NULL COMMENT ' 회원사 정보 키 cum_company.c_num',
 `reg_day` datetime NOT NULL COMMENT '생성일자',
 `mod_day` datetime NOT NULL COMMENT '수정일자    ',
 `cs_state` tinyint(2) unsigned NOT NULL COMMENT '회원상태:1=정상회원,2=일지정지회원, 3=삭제회원',
 `cs_permission` tinyint(2) unsigned NOT NULL COMMENT '권한:1=수퍼관리자, 2=입력수정삭제, 3=VIEWER , 4=공급업체',
 `cs_id` char(14) CHARACTER SET utf8 NOT NULL COMMENT '로그인아이디    ',
 `cs_pw` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '로그인패스워드    ',
 `cs_db` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '사용자 연결DB',
 `cs_name` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '담당자명    ',
 `cs_department` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '부서    ',
 `cs_mail` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '담당자메일    ',
 `cs_phone` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '담당자연락처    ',
 `cs_memo` text COLLATE utf8_bin NOT NULL COMMENT '메모사항    ',
 `cs_join_day` datetime NOT NULL COMMENT '최종접속일    ',
 `cs_join_ip` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '최종접속아이피    ',
 `cs_counter` smallint(5) NOT NULL COMMENT '로그인횟수    ',
 `login_type` tinyint(2) unsigned NOT NULL COMMENT '접속상태:1=접속중, 0=로그아웃',
 PRIMARY KEY (`cs_num`),
 KEY `c_num` (`c_num`,`cs_state`)
) ENGINE=MyISAM AUTO_INCREMENT=273 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='회원사_회원목록'

 


by 함박눈 [2016.01.28 15:36:55]

답변 감사합니다

올려주신 1번,2번 쿼리를 수행해 보았는데 칼럼이 애매하다는 에러가 나더라구요 

알리아스를 주었는데 왜 이런 에러메시지가 뜨는지 모르겠네요;;


by DeSSa [2016.01.28 16:36:20]
1. 스키마 상으로 cum_company가 명세 테이블로 보이며 master_login_staff 테이블이 cum_company를 참조해서 정보를 가져와야 하는 구조로 보입니다.
SELECT count(mls.cs_id)
FROM master_login_staff AS mls 
LEFT JOIN cum_company AS cc ON mls.c_num = mls.c_num
WHERE mls.cs_id = mls.cs_db

2. 쿼리의 용도 확인?
[ex.1] company에 속해 있는 모든 스태프 정보.
companyA     staff1
companyA     staff2
companyA     staff3
companyB     staff4
companyB     staff5
companyB     staff6

[ex.2] staff들이 속해 있는 company의 모든 정보.
staff1      companyA
staff2      companyC
staff3      companyB
staff4      companyE
staff5      companyD
staff6      companyF

 


by 함박눈 [2016.01.28 16:58:04]
답변 감사합니다~ 답변주신대로 아래와 같이 쿼리를 날려보았습니다

SELECT SQL_CALC_FOUND_ROWS * FROM cms_master_login.master_login_staff
 AS CL 
left join cms_master_login.cum_company as CC on CL.c_num = CC.c_num 
where CL.cs_id = CL.cs_db ORDER BY CC.reg_day DESC

그런데 총 회원사가 228개여서 228개의 레코드가 출력되야 하는데 193개의 레코드가 출력되었습니다. 
오히려 더 적은 수의 레코드가 출력되네요 
왜 이런 현상이 발생하는지 모르겠습니다 흑ㅠ

 


by DeSSa [2016.01.28 17:29:19]
SELECT SQL_CALC_FOUND_ROWS * FROM cms_master_login.master_login_staff AS CL
LEFT JOIN cms_master_login.cum_company as CC on CL.c_num = CC.c_num
ORDER BY CC.reg_day DESC

위와 같이 WHERE절에 조건을 주지 않고 228개가 호출 되시는지 보시겠어요??

228개가 출력 되지 않는 다면 cs_id = cs_cd가 동일한 staff가

[TBL_company]

c_num

   1

   2

   3

 

[TBL_customer]

 cnum    cs_id   cs_cd

   1        aaa     aaa

   2        aab     aac

   3        bbb    bbb

이러한 데이터 형태를 뛸 것으로 예상 됩니다.

 

위에 테이블에서 작성자 님이 사용하신 쿼리를 사용 하면 결과를 3개를 바라지만 2개(1, 3값만나옴)가 나오는 쿼리 구조입니다.

즉, 228개를 다 채우기 위해서는

cms_master_login.master_login_staff 테이블에 검색되지 않은 c_num에 대한 staff정보를 넣어주셔야 할것 같네요.

c_num      cs_id     cs_db

   2          ccc        ccc

위에 예시에 이러한 정보가 추가 된다면 결과가 3개가 도출 될 것입니다.

 

마지막으로,

SELECT SQL_CALC_FOUND_ROWS * FROM cms_master_login.master_login_staff AS CL
LEFT JOIN cms_master_login.cum_company as CC on CL.c_num = CC.c_num
WHERE (CL.cs_id = CL.cs_db) OR (CL.cs_id <> CL.cs_db)
ORDER BY CC.reg_day DESC

이렇게 호출 하시면 228개가 나올듯 합니다.

 

 

 

 

 

 


by 함박눈 [2016.01.28 18:35:11]
답변 감사합니다 :)
보내주신 아래 쿼리문을 적용해 보았습니다.
SELECT SQL_CALC_FOUND_ROWS * FROM cms_master_login.master_login_staff AS CL
LEFT JOIN cms_master_login.cum_company as CC on CL.c_num = CC.c_num
WHERE (CL.cs_id = CL.cs_db) OR (CL.cs_id <> CL.cs_db)
ORDER BY CC.reg_day DESC

총 205개의 레코드가 출력되더라구요 
조인을 써서 그런건지 모르겠지만 원하는대로 데이터가 안불러와 지네요ㅠ;;
저도 서브쿼리 등 다양한 방법 찾아보겠습니다(도와주셔서 감사해요^-^)

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입