Catch Surf, Beater, Twin Fin, Single Fin, Blackball Beater
Catch Surf, Beater, Twin Fin, Single Fin, Blackball Beater
SoftBoards make the wonderful world of surfing seem a little less dangerous for young kids and beginners, sort through our selection of softboards by Catch Surf for a beginner or a black ball beater then GET ON IT!
SELECT
`l`.`id` AS `label_id`,
`l`.`name` AS `label`,
`v`.`thumb_1` AS `thumb`,
`v`.`thumb_2` AS `altthumb`,
`s`.`department_id`,
`s`.`category_id`,
`s`.`subcategory_id`,
`sub`.`name`AS`subcategory`,
`cat`.`name`AS`category`,
`s`.`style`,
`s`.`title`,
`s`.`isnew`,
`s`.`low_price_list` AS `price_list`,
`s`.`low_price_sell` AS `price_sell`,
`s`.`ctime`,
`s`.`qty` AS `inventory`,
`v`.`color`,
`s`.`id` AS `style_id`,
`v`.`id` AS `variant_id`
FROM `style` `s`
INNER
JOIN `variant` `v`
ON (`v`.`style_id`=`s`.`id` AND `v`.`isactive`=1 AND `v`.`isdefault`=1)
INNER
JOIN `category` `sub`
ON (`s`.`subcategory_id`=`sub`.`id`)
INNER
JOIN `category` `cat`
ON (`s`.`category_id`=`cat`.`id`)
INNER
JOIN `label` `l`
ON (`s`.`label_id`=`l`.`id`)
WHERE `s`.`isactive`=1 AND `s`.`issite_jacks`=1 AND `s`.`subcategory_id`='991'
ORDER BY `s`.`isnew` DESC, `s`.`date` DESC
LIMIT 0, 60
;
United States ($)
Canada (C$)
Australia ($)
United Kingdom (£)
Japan (¥)
Germany (€)
Show all Currencies
