SELECT /* MAIN SEARCH QUERY */
	
	pm.name AS manufacturer,
	pm.name AS manufacturer_name,
	P.id,
	P.id AS product_id,
	P.code,
    P.cost_price,
    P.vat_enabled,
	P.description,
	P.sold_retail,
	P.sold_trade,
	P.sold_wholesale,
    P.country_origin_code,
    P.commodity_code,
    P.price_set,
    P.width,
    P.height,
    P.length,
    P.product_scale_id,
    P.reorder_level,
    P.release_date,
    P.weight,
    P.restriction,
    P.restriction_bit,
    P.active,
    P.minimum_level,
    P.second_hand,
    P.limited_availability,
    P.can_reorder,
    P.warehouse_zone_id,
    P.wholesale_customers,
    P.new_price,
    P.big_postage,
    P.disable_min_order_amount,
    P.last_updated,
    P.notes,
    P.old_product_scale_txt,
    P.has_image_200,
    P.has_image_400,
    P.last_purchase_order_stock,
    P.last_purchase_order_date,
    P.first_delivered_date,
    P.last_moved,
    P.scale_string,
    P.keywords,
    P.finished_date,
    P.availability,
    P.sold,
    P.sold_total,
    P.sold_retail,
    P.sold_trade,
    P.sold_wholesale,
	P.base_price,
	P.base_export_price,
	P.retail_discount,
	P.eu_price,
	P.export_price,
	P.trade_price,
	P.trade_export,
	P.wholesale_price,
	P.wholesale_export,
	P.discount,
	P.backorder_retail_discount,
	P.backorder_discount,
	P.trade_discount,
	P.wholesale_discount,
	P.product_category_id,
    P.product_division_id,
	P.product_type_id,
	P.product_manufacturer_id,
	GROUP_CONCAT(DISTINCT ps.scale ORDER BY ps.scale ASC SEPARATOR ', ') AS scale,
	UNIX_TIMESTAMP(P.date_added) AS date_added,
	UNIX_TIMESTAMP(P.arrival_date) AS arrival_date ,
	DATE(P.arrival_date) AS real_arrival_date ,
	pt.name AS product_type,
	pt.name AS type_name,
	media.filename,
	IF (pp.id is null,0,1) as pending,
	
	if(
		coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) ) , 0 ) >= 0  ,
		if(
			coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) - coalesce(bv.backorders, 0 ) - coalesce(bv.free_backorders, 0 ) ) , 0 ) >= P.show_stock,
			P.show_stock ,
			coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) - coalesce(bv.backorders, 0 ) - coalesce(bv.free_backorders, 0 ) ) , 0 )
		)
	    , 0
	) as available,
    COALESCE(sl.count,0) as stock_level,
    COALESCE(sc.product_quantity,0) AS stock_committed,
    COALESCE(bv.backorders,0) AS backorders,
    COALESCE(bv.free_backorders,0) AS free_backorders,
	pc.id as department_id,
	pc.name as department,
	pc.name as department_name,
	P.show_stock,
	if(
		pc.name = 'Future Releases',
		true,
		false
	) AS future,
    unix_timestamp(media.date_uploaded) as unixtime



FROM product P
LEFT JOIN stock_level sl on P.id = sl.product_id
LEFT JOIN stock_committed sc on sc.product_id = P.id
LEFT JOIN backorder_values bv ON bv.product_id = P.id
LEFT JOIN product_category pc on P.product_category_id = pc.id
LEFT JOIN product_manufacturer pm ON P.product_manufacturer_id = pm.id
LEFT JOIN product_media media ON P.id = media.product_id AND `media`.`order` = 0

JOIN
product_scale_map AS psm ON (psm.product_id = P.id)

LEFT JOIN
product_scale ps ON (psm.scale_id = ps.id)

LEFT JOIN product_type pt ON P.product_type_id = pt.id
LEFT JOIN pending_product pp ON P.id = pp.id
WHERE 1  AND P.restriction_bit & 1 = 1 AND P.product_manufacturer_id=362588 AND P.price_set = 1 
		 AND P.product_category_id = 102 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE(P.arrival_date) desc, P.code
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 135 results
| View per page
Refine your search

Deprecated: Creation of dynamic property Hannants\SearchFilters::$searchDirection is deprecated in /mnt/vault/Sync/Dessol/Sites/hannants.co.uk/php_includes/classes/SearchFilters.php on line 115
Manufacturer & Catalogue No ASC DESC
Scale ASC DESC
Description ASC DESC
Type ASC DESC
Department ASC DESC
Price ASC DESC
Watch
Stock ASC DESC
Buy

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMF14411
Supermarine Spitfire roundels & fin flashes, Pt.2, 2 sets insignia diameters: Type B (56"), C (32, 56"), C1 (36, 56"), D (30, 32, 36, 50"),SEAC (16") scale diameters: Type B (9.9 mm), C (5.6;9.9 mm), C1 (6.4;9.9 mm), D (5.3;5.6;6.4;8.8 mm),SEAC (2.8 mm) fin flashes: 22x22", 24x24", 24x16", 24x18" scale width: 3.9x3.9; 4.2x4.2; 4.2x2.8; 4.2x3.2 mm
Aircraft decals (military)
Catalogue
£3.60
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK144107
1:144
Portuguese AF Insignia, 2 sets
Aircraft decals (military)
Catalogue
£3.60
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMF14409
de Havilland Canada DHC-2 Beaver in Finnish AF Decals for the following aircraft are included: BV-1 BV-2 BV-3 These decals were designed to match 1/144 Miniwing kit.
Aircraft decals (military)
Catalogue
£3.60
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMF14410
1:144
McDonnell-Douglas F/A-18C/D Hornet in Finnish AF Decals for the following aircraft are included: HN-421 (F/A-18C) HN-424 (F/A-18C) HN-431 (F/A-18C) HN-450 (F/A-18C) HN-457 "Kreivi von Rossen" (F/A-18C) HN-462 (F/A-18D) HN-466 (F/A-18D)
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK144106
USAF lettering - Blue, 2 sets letter heights: 4,6,8,10,12,18,24,36" scale heights: 0.7;1.0;1.4;1.8;2.1;3.2;4.2;6.4 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMS06
1:100, 1:144, 1:35, 1:48, 1:72
Formation-keeping lights - modern, 2 sets Scale sizes: 1.7x0.5;3.3x0.5;4.8x0.5 mm
Military vehicle decals
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK7210
SNCASE SE.535 Mistral (3x camo schemes), for Azur-FRROM
Aircraft decals (military)
Catalogue
£8.99
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK7211
Bristol Sycamore (5x camo schemes), for S&M, DF HeloStuff, CMR
Aircraft decals (military)
Catalogue
£8.99
-
In Stock
3

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK144101
Luftwaffe Code Letters - White, 2 sets Letter height: 600 mm Scale height: 4.2 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK144102
Luftwaffe Code Letters - Yellow, 2 sets Letter height: 600 mm Scale height: 4.2 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK144103
Luftwaffe Code Letters - Red, 2 sets Letter height: 600 mm Scale height: 4.2 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK144104
Luftwaffe Code Letters - Blue, 2 sets Letter height: 600 mm Scale height: 4.2 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK144105
Luftwaffe Code Letters - Green, 2 sets Letter height: 600 mm Scale height: 4.2 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK14499
Norwegian AF roundels, 2 sets Insignia diameters: 400, 550, 700, 800, 900, 1000 mm Scale diameters: 2.8;3.8;4.9;5.6;6.3;6.9 mm
Aircraft decals (military)
Catalogue
£3.60
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMF14407
Lockheed U-2R serial numbers Decals for the following aircraft are included: 10329 10330 10331 10332 10333 10334 10335 10336 10337 10338 10339 10340 These decals were designed to match 1/144 Dragon kit.
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
4

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMF14408
Lockheed TR-1 Tail Art Decals for the following aircraft are included: 01074 01084 01087 01098 01099 These decals were designed to match 1/144 Dragon kit.
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
2

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMF7201
No.1435 Flt. Panavia Tornado F.3 No.1435 Flt. Tornado F.3 Tornado F.3s, based at RAF Mount Pleasant, provide air defence for the Falkland Islands, South Georgia and the South Sandwich Islands. Decals for the following aircraft are included: ZE812/F ZG780/H ZG751/C ZG798/F ZE889/H ZG799/D ZE736/F ZG778/H ZE834/C ZG797/D These decals were designed to match 1/72 Airfix, Hasegawa, Italeri, Matchbox and Revell kits.
Aircraft decals (military)
Catalogue
£6.40
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMF14405
No.1435 Flt. Panavia Tornado F.3 Tornado F.3s, based at RAF Mount Pleasant, provide air defence for the Falkland Islands, South Georgia and the South Sandwich Islands. Decals for the following aircraft are included: ZE812/F ZG780/H ZG751/C ZG798/F ZE889/H ZG799/D ZE736/F ZG778/H ZE834/C ZG797/D These decals were designed to match 1/144 Revell and Dragon kits.
Aircraft decals (military)
Catalogue
£3.60
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK7206
Luftwaffe Swastikas, encircled - White background, 2 sets insignia diameters: 500,600,700,800,1000,1200 mm scale diameters: 6.9;8.3;9.7;11.1;13.1;16.7 mm
Aircraft decals (military)
Catalogue
£5.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK7207
1:72
British WWI roundels, 2 sets Insignia diameters: 24,38,48,58,62,68,76"; 23,27,31" Tricolour stripes Scale diameters: 8.5;13.4;16.9;20.5;21.9;24.0;26.8 mm; Stripes: 8.1;9.5;10.9 mm
Aircraft decals (military)
Catalogue
£5.80
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK7208
British WWI Night Fighter roundels Pt.1, 2 sets Insignia diameters: 24,38,48,58,62,68"; 23,27,31" Tricolour stripes scale diameters: 8.5;13.4;16.9;20.5;21.9;24.0;26.8 mm; stripes: 8.1;9.5;10.9 mm
Aircraft decals (military)
Catalogue
£5.80
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK7209
British WWI Night Fighter roundels Pt.2, 2 sets Insignia diameters: 24,38,48,58,62,68"; 15,18,21" tricolour stripes scale diameters: 8.5;13.4;16.9;20.5;21.9;24.0;26.8 mm; stripes: 5.3;6.4;7.4 mm
Aircraft decals (military)
Catalogue
£5.80
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK14495
1:144
British WWI roundels, 2 sets insignia diameters: 24,38,48,58,62,68,76"; 23,27,31" tricolour stripes scale diameters: 4.2;6.7;8,5;10,2;10,9;12,0;13,4 mm; stripes: 4.1,4.8,5.5 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK14496
1:144
British WWI Night Fighter roundels Pt.1, 2 sets insignia diameters: 24,38,48,58,62,68"; 23,27,31" tricolour stripes scale diameters: 4.2;6.7;8,5;10,2;10,9;12,0 mm; stripes: 4.1,4.8,5.5 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Mark I Decals DMK14497
British WWI Night Fighter roundels Pt.2, 2 sets insignia diameters: 24,38,48,58,62,68"; 15,18,21" tricolour stripes scale diameters: 4.2;6.7;8,5;10,2;10,9;12,0 mm; stripes: 2.7,3.2,3.7 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
5+

   1   |   2   |   3   |   4   |   5      »      [6]

Search Area Manufacturer Item Type Search

Part Number Scale Keywords Results per Page
Exact Match
Sort by:
Sort Direction:
Added Within last week
last 2 weeks
last month
last 6 months
last year