SQL

SELECT * , GRP.URLNAME AS GRPURLNAME, (CASE GRP.BRACODE WHEN 8 THEN 99 WHEN 9 THEN 98 WHEN 10 THEN 97 WHEN 3 THEN 96 WHEN 5 THEN 95 WHEN 11 THEN 94 ELSE 50 END)AS BRAORDER FROM M_SHOHINGRP GRP LEFT OUTER JOIN M_JYOKEN JYO ON JYO.GRPCODE = GRP.GRPCODE LEFT OUTER JOIN M_BRAND BRA ON BRA.BRACODE = GRP.BRACODE LEFT OUTER JOIN M_ECHIN ECH ON ECH.GRPCODE = GRP.GRPCODE 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 JYO.MAKCODE = 2 AND JYO.SHACODE = 42 AND JYO.KATCODE = 1 AND GRP.HYOJIFLG = 1 AND NOT COALESCE( CNT.COUNTS, '0') = 0 AND GRP.BNRCODE = 1 AND GRP.BRACODE = 12 ORDER BY BRAORDER DESC,GRP.BRACODE,GRP.SORTORDER DESC,GRPNAME

Array ( [item00000376] => Array ( [NAME] => C-HR テールランプ [NAMEEN] => C-HR テールランプ [ORDER] => [GRPURLNAME] => item00000376 [BRANAMEEN] => G-FACTORYORIGINAL [PRODUCTID] => 421 [COUNT] => 2 ) [item00000383] => Array ( [NAME] => C-HR テールランプ セット [NAMEEN] => C-HR テールランプ セット [ORDER] => [GRPURLNAME] => item00000383 [BRANAMEEN] => G-FACTORYORIGINAL [PRODUCTID] => 428 [COUNT] => 5 ) [item00000382] => Array ( [NAME] => C-HR トランクスポイラーランプ [NAMEEN] => C-HR トランクスポイラーランプ [ORDER] => [GRPURLNAME] => item00000382 [BRANAMEEN] => G-FACTORYORIGINAL [PRODUCTID] => 427 [COUNT] => 8 ) )

Array ( [slug] => item00000376 [item_type] => aero [brand_code] => 12 [maker_code] => 2 [shashu_code] => 42 [katashiki_code] => 1 [productid] => 421 )

Array ( [slug] => item00000383 [item_type] => aero [brand_code] => 12 [maker_code] => 2 [shashu_code] => 42 [katashiki_code] => 1 [productid] => 428 )

Array ( [slug] => item00000382 [item_type] => aero [brand_code] => 12 [maker_code] => 2 [shashu_code] => 42 [katashiki_code] => 1 [productid] => 427 )

SQL

SELECT * , GRP.URLNAME AS GRPURLNAME, (CASE GRP.BRACODE WHEN 8 THEN 99 WHEN 9 THEN 98 WHEN 10 THEN 97 WHEN 3 THEN 96 WHEN 5 THEN 95 WHEN 11 THEN 94 ELSE 50 END)AS BRAORDER FROM M_SHOHINGRP GRP LEFT OUTER JOIN M_JYOKEN JYO ON JYO.GRPCODE = GRP.GRPCODE LEFT OUTER JOIN M_BRAND BRA ON BRA.BRACODE = GRP.BRACODE LEFT OUTER JOIN M_ECHIN ECH ON ECH.GRPCODE = GRP.GRPCODE 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 JYO.MAKCODE = 2 AND JYO.SHACODE = 42 AND JYO.KATCODE = 1 AND GRP.HYOJIFLG = 1 AND NOT COALESCE( CNT.COUNTS, '0') = 0 AND GRP.BNRCODE = 4 AND GRP.BRACODE = 12 ORDER BY BRAORDER DESC,BRA.SORTORDER DESC,GRP.SORTORDER DESC,GRPNAME