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=353232 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 31 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

Astra Decals ASD4807
Limited Re-release! Lockheed-Martin F-16C Aviano. 89-001 31FW Flagship. Includes four versions from 1994 to 1998 with blue and low vis Sea Horse on fin
Aircraft decals (military)
Catalogue
£13.50
-
In Stock
20+

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

Astra Decals ASD4808
1:48
Limited Re-release! Lockheed-Martin F-16C Aviano. 88-413 510FS Buzzards Flagship; 89-491 510FS; 89-050 50th Anni 1997 scheme
Aircraft decals (military)
Catalogue
£13.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

Astra Decals ASD4809
1:48
Limited Re-release! Lockheed-Martin F-16C Aviano. 88-550 555FS Triple Nickel Flagship 2 versions pre 1999; 89-2035 post 1999 and any squadron aircraft. 3 Eagle head designs
Aircraft decals (military)
Catalogue
£13.50
-
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

Astra Decals ASD7207
Limited Re-release! Lockheed-Martin F-16C Aviano. 89-001 31FW Flagship. Includes four versions from 1994 to 1998 with blue and low vis Sea Horse on fin
Aircraft decals (military)
Catalogue
£9.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

Astra Decals ASD7208
1:72
Limited Re-release! Lockheed-Martin F-16C Aviano. 88-413 510FS Buzzards Flagship; 89-491 510FS; 89-050 50th Anni 1997 scheme
Aircraft decals (military)
Catalogue
£9.20
-
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

Astra Decals ASD7209
1:72
Limited Re-release! Lockheed-Martin F-16C Aviano. 88-550 555FS Triple Nickel Flagship 2 versions pre 1999; 89-2035 post 1999 and any squadron aircraft. 3 Eagle head designs
Aircraft decals (military)
Catalogue
£9.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

Astra Decals ASD3209
Lockheed-Martin F-16C Aviano Part 2. 89-2016 16 Air Force; 88-550 555FS Triple Nickel Flagship 2 versions pre 1999; 89-2035 post 1999 and any squadron aircraft. 3 Eagle head designs; 87-0351 31 OSS
Aircraft decals (military)
Catalogue
£15.50
-
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

Astra Decals ASD4820
1:48
General-Dynamics F-16B MLU Fighting Falcon Royal Netherlands Air Force
Aircraft detailing sets (decal and resin)
Catalogue
£13.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

Astra Decals ASD4822
1:48
General-Dynamics F-16A/F-16B. Lockheed-Martin F-16C/F-16D Reinforcement plates (these are made from vinyl)
Aircraft detailing sets (paper)
Catalogue
£10.30
-
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

Astra Decals ASD3222
1:32
General-Dynamics F-16A/F-16B. Lockheed-Martin F-16C/F-16D Reinforcement plates x 1 set (these are made from vinyl)
Aircraft decals (military)
Catalogue
£10.30
-
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

Astra Decals ASD7222
General-Dynamics F-16A/F-16B. Lockheed-Martin F-16C/F-16D Reinforcement plates x 3 sets (these are made from vinyl)
Aircraft detailing sets (vinyl)
Catalogue
£10.30
-
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

Astra Decals ASD14416
1:144
Lockheed F-104G Starfighter German decals, the most researched decals ever. Containing over 1400 separate decals this set will allow you to make each and every German Starfighter in what ever paint scheme it ever flew including specials, making this the most complete decal set ever. Includes five decal sheet and a full instruction booklet
Aircraft decals (military)
Catalogue
£10.30
-
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

Astra Decals ASD4819
General-Dynamics F-16A MLU Fighting Falcon Royal Netherlands Air Force 323 Sqn 'Diana' Part 2 j-881 60TH Anniversary (2008-2009) summer and winter scheme
Aircraft decals (military)
Catalogue
£12.99
-
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

Astra Decals ASD14418
1:144
General-Dynamics F-16A Block 15 Fighting Falcon Royal Netherlands Air Force 323 Sqn 'Dirty Diana' J-248: 50th Aniversary 1998-99
Aircraft decals (military)
Catalogue
£3.60
-
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

Astra Decals ASD14419
1:144
General-Dynamics F-16A MLU Fighting Falcon Royal Netherlands Air Force 323 Sqn 'Diana' Part 2 J-881 60th Anniversary (2008-'09) Summer and Winter Scheme
Aircraft decals (military)
Catalogue
£4.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

Astra Decals ASD7214
1:72
Lockheed-Martin F-16C/Lockheed-Martin F-16D Fighting Falcon USAFE Spangdahlem SP (3) 90-828 22FS Flagship; 90-843 22FS linejet; 96-080 23rd FE Flagship; 91-412 linejet. Numbers included to make most squadron aircraft.
Aircraft decals (military)
Catalogue
£7.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

Astra Decals ASD4816
1:48
German Lockheed F-104G Starfighters decals, the most researched decals ever. Containing over 1400 separate decals this set will allow you to make each and every German Starfighter in What ever paint scheme it ever flew including specials, making this the most complete decal set ever. Includes five decal sheet and a full instruction booklet
Aircraft decals (military)
Catalogue
£30.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

Astra Decals ASD7216
1:72
German Lockheed F-104G Starfighters decals, the most researched decals ever. Containing over 1400 separate decals this set will allow you to make each and every German Starfighter in What ever paint scheme it ever flew including specials, making this the most complete decal set ever. Includes five decal sheet and a full instruction booklet
Aircraft decals (military)
Catalogue
£20.64
-
In Stock
1

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

Astra Decals ASD4818
1:48
General-Dynamics F-16A Block 15 Fighting Falcon Royal Netherlands Air Force 323 Sqn 'Dirty Diana' J-248: 50th Aniversary 1998-99
Aircraft decals (military)
Catalogue
£12.99
-
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

Astra Decals ASD7218
1:72
General-Dynamics F-16A Block 15 Fighting Falcon Royal Netherlands Air Force 323 Sqn 'Dirty Diana'
Aircraft decals (military)
Catalogue
£8.80
-
In Stock
1

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

Astra Decals ASD7219
1:72
General-Dynamics F-16A MLU Fighting Falcon Royal Netherlands Air Force 323 Sqn 'Dirty Diana' Part 2
Aircraft decals (military)
Catalogue
£8.80
-
In Stock
1

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

Astra Decals ASD7220
1:72
General-Dynamics F-16B MLU Fighting Falcon Royal Netherlands Air Force
Aircraft detailing sets (decal and resin)
Catalogue
£8.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

Astra Decals ASD3214
1:32
Lockheed-Martin F-16C/Lockheed-Martin F-16D Fighting Falcon USAFE Spangdalen. (Updated version of ASD3203)
Aircraft decals (military)
Catalogue
£12.99
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

Astra Decals ASD4812
1:48
Lockheed-Martin F-16C Aviano. 89-2016 16AF; 87-0351 31OSS; 89-2137 31 OG Flagships. All May 2000;
Aircraft decals (military)
Catalogue
£10.30
-
In Stock
1

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

Astra Decals ASD7215
1:72
Boeing F/A-18F Super Hornet VFA-102 (3) 165892 NF/102 VFA-102 Diamondbacks 50th Anniversary scheme 2005 NAF Atsugi or standard scheme with coloured fuselage band 2007; 165894 NF/100 CAG scheme 2007; 165888 NF/106 standard scheme
Aircraft decals (military)
Catalogue
£12.99
-
In Stock
4

   1   |   2      »      [2]

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