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=8224
		 AND P.product_category_id = 114 
		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
Special Offers | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Special Offers



Found 18 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

Superscale SS32094
1:32
McDonnell-Douglas F/A-18A Hornet (1) 162422 AK/200 VFA-132 CAG SS32096 Lockheed-Martin F-16C (2) 88-401 401 TFW Wing CO; 87-221 614 TFS Squadron CO, TJ Torrejon WAS é£4.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£3.33
-
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

Superscale SS32115
1:32
McDonnell-Douglas F/A-18A Hornet McDonnell-Douglas F/A-18C (2) 161930 VFA-203 Blue Dolphins AF/313; 163493 VFA-83 Rampagers AA/306 USS Saratoga . WAS é£4.25. TEMPORARILY REDUCED BY 1/3RD!!!
Aircraft decals (military)
Special Offers
£2.83
-
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

Superscale SS32124
1:32
McDonnell-Douglas F/A-18C Hornet (1) 164252 AC/307 VFA-37 Bulls USS JFK WAS é£7.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£5.33
-
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

Superscale SS32130
1:32
McDonnell-Douglas F/A-18C (1) AG/300 Knighthawks WAS é£4.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£3.33
-
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

Superscale SS32228
1:32
McDonnell-Douglas F/A-18C Hornet (1) 163769/000 VFA-97 CAG Yellow/green markings . WAS é£8.60. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£5.73
-
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

Superscale SS32238
1:32
McDonnell-Douglas F/A-18+ hornet (1) 162401 MF/01 VMFA-134 Smoke CO red/yellow markings . WAS é£8.60. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£5.73
-
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

Superscale SS32253
1:32
Lockheed RF-80A Shooting Stars (2) WAS é£11.99. NOW BEING CLEARED!! SAVE 1/3RD!!! 48-5467 FT-467 45th TRS, Kimpo AB, 1952; 45-8375 FT-375, 'Ball of Fire' (veteran of more than 100 missions)15th TRS, Kimpo AB,1953. Includes color profiles and upper plan views, plus painting reference. For Czech Model kit CM3202 in combination with True Details Lockheed RF-80A nose conversion TD32460.
Aircraft decals (military)
Special Offers
£7.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

Superscale SS44020
Boeing 707 (3) ZAMBIA AIRWAYS 9J-ADV; IRAQI AIRWAYS; SUDAN ST-AFA 'Blue Nile' WAS é£4.99. NOW BEING CLEARED!!! SILLY PRICE!!!
Aircraft decals (civil)
Special Offers
£0.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

Superscale SS480184
1:48
Lockheed T-33 Shooting Star (3) Dominican Air Force 1969 FAD-3306; Uruguay 1968 206; Peru Air Force . WAS é£7.76. NEARLY HALF PRICE!!!
Aircraft decals (military)
Special Offers
£3.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

Superscale SS480608
1:48
Lockheed P-38H (2) White 130 Lt Don Bellows 'Piss Pot Pete; 113 Lt Champlin 'Buffalo Blitz'431 FS/475FG . WAS é£5.00. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£3.33
-
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

Superscale SS480609
1:48
McDonnell-Douglas F/A-18D Hornet (2) 161938 MG/00 VMFA-321 Hell's Angels; 162864 NJ/326 VFA-125 Vikings Training Sqn, NAS Lemore WAS é£7.99. NOW BEING CLEARED!! HALF PRICE!!!
Aircraft decals (military)
Special Offers
£3.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

Superscale SS480621
1:48
Lockheed-Martin F-16C 50th Anniversary (2) 170thFS/182FW Illinois ANG; 149FS/192FS Virginia ANG . WAS é£5.00. TEMPORARILY HALF PRICE!!!
Aircraft decals (military)
Special Offers
£2.50
-
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

Superscale SS480983
1:48
McDonnell-Douglas F/A-18C (1) 164716 NE/300 VFA-151 Vigilantes CAG USS Abrahan Lincoln lack fin with yellow trim WAS é£7.20. TEMPORARILY SAVE 1/3RD!!!)
Aircraft decals (military)
Special Offers
£4.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

Superscale SS481038
1:48
McDonnell-Douglas F/A-18A Hornet+ 163133 AC/200 VMFA-115 Silver Eagles with colourful CAG scheme with Stars and Stripes on fins . WAS é£7.20. TEMPORARILY REDUCED BY 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Superscale SS481052
1:48
McDonnell-Douglas F/A-18C Hornets (2) 163761 AD/301 VFA-106 Gladiators with Blue markings; 163769/000 VFA-97 CAG Yellow/green markings WAS é£7.20. TEMPORARILY SAVE 1/3RD!!!)
Aircraft decals (military)
Special Offers
£4.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

Superscale SS481104
1:48
Republic P-47D Thunderbolt (2) 'Razorback' 226299 UN-B 63 FS/56 FG Lt C.Hart OD/Grey; Bubble X65 86 FS/79 FG `The Trojan Horse' . WAS é£7.20. TEMPORARILY REDUCED BY 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Superscale SS481138
Republic P-47C/P-47D Thunderbolt 'Razorback' (2) Republic P-47D Thunderbolt 42-75151 HQ.Flt. 1st Combat BW Formation Monitor Republic P-47C 41-6630 453rd BW (Heavy) Formation Monitor BT. WAS é£7.20. NOW BEING CLEARED!! SAVE 1/3RD!!!)
Aircraft decals (military)
Special Offers
£4.80
-
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
Superscale SS48S3
Lockheed S-3A/S-3B Viking Jet Intake warning signs. 2 grey, 2 black and 2 red. (These were printed as correction sheets for SS480689 and SS480690) No bag. No instructions. Decal only.
Aircraft decals (military)
Special Offers
£0.99
-
In Stock
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