SELECT MAK.* , SHA.* , SHA.URLNAME SHAURLNAME , SHA.URLNAME SHAURLNAME , GRP.GRPNAME FROM M_SHOHINGRP GRP LEFT OUTER JOIN M_JYOKEN JYO ON JYO.GRPCODE = GRP.GRPCODE LEFT OUTER JOIN M_MAKER MAK ON JYO.MAKCODE = MAK.MAKCODE LEFT OUTER JOIN M_SHASHU SHA ON JYO.MAKCODE = SHA.MAKCODE AND JYO.SHACODE = SHA.SHACODE LEFT OUTER JOIN ( SELECT GRPCODE, COUNT(HINCODE) AS COUNTS FROM M_HIN WHERE ECTORIFLG = 1 AND HINTEISHI IS NULL GROUP BY GRPCODE ) CNT ON CNT.GRPCODE = GRP.GRPCODE WHERE GRP.BRACODE = 2 AND GRP.HYOJIFLG = 1 AND NOT COALESCE( CNT.COUNTS, '0') = 0 GROUP BY SHA.MAKCODE , SHA.SHACODE ORDER BY SHA.SORTORDER DESC
車種別検索
