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 ps.id = 957 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 123 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 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 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 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+

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 DMK14498
RAF rank flags, 2 sets RAF flag size: 58x29"; rectangular flag, swallow-tailed flag & pennant sizes: 10x7" scale sizes: RAF flag: 10.2x5.1; other flags: 1.8x1.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 DMF14406
Jagdwaffe Emblems, Pt.1 Decals for the following units are included: I/JG 1,II/JG 1,III/JG 1,4/JG 1,6/JG 1 JG 1,Stab/JG 1,JG 2,III/JG 2,7/JG 2,10(JaBo)/JG 2 JG 3,Stab/JG 3,2/JG 3,II/JG 3,III/JG 3 JG 4,JG 5,II/JG 5,III/JG 5,6/JG 5,7/JG 5,JG 6,JG 7 JG 21,1/JG 21,2/JG 21,JG 26,Stab/JG 26,7/JG 26,9/JG 26 I/JG 27,3/JG 27,7/JG 27,1/JGr 50
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 DMK14493
Lavochkin La-5/La-7 La-5/7 bort numerals - White, 2 sets numeral height: 920 mm, Red and Blue outline scale height: 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 DMK14494
1:144
RAAF Low-vis roundels, 2 sets insignia diameters: 18,22,24,36" scale diameters: 3.2;3.9;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 DMF14404
1:144
No.1435 Flt. Typhoon FGR.4 Typhoon FGR.4s, 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: ZJ915/F ZJ929/H ZJ933/C ZJ941/D ZJ944/F ZJ949/H ZJ950 ZJ950/C ZK301/D These decals were designed to match 1/144 Revell, Platz and F-Toys kits.
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 DMK14492
1:144
RAF Prototype 'P' Markings, 2 sets. Markings dia 25, 30, 36, 54". scale dia 4.4; 5.3; 6.4; 9.5 mm
Aircraft decals (military)
Catalogue
£2.70
-
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 DMF14401
Supermarine Spitfire roundels & fin flashes, Pt.1, 2 sets roundels Type A (25,35,40,50 inches), A1 (31,35,49,56 inches), B (15,25,35,40,56 inches) scale diameter: Type A (4.4;6.2;7.08.8 mm), A1 (5.5;6.2;8.6;9.9 mm), B (2.7;4.4;6.2;7.0;9.9 mm) fin flash: width 21 inches and 24 inches scale width: 3.7 mm and 4.2 mm
Aircraft decals (military)
Catalogue
£3.20
-
In Stock
4

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

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