English
SELECT m.manufacturers_name, p.products_image, pd.products_name, p.products_quantity, p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, p.products_model,
IF(s.status = 1, s.specials_new_products_price, NULL) AS specials_new_products_price,
IF(s.status = 1, s.specials_new_products_price, p.products_price) AS final_price,
p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, m.manufacturers_name, p.products_date_added, p.products_date_available
FROM products p
LEFT JOIN specials s ON s.products_id = p.products_id
LEFT JOIN products_description pd ON pd.products_id = p.products_id
AND pd.language_id = 1
LEFT JOIN products_to_categories p2c ON p2c.products_id = p.products_id
LEFT JOIN manufacturers m ON m.manufacturers_id = p.manufacturers_id
WHERE p.products_status = 1
AND p2c.categories_id = 310
ORDER BY p.products_sort_order, pd.products_name
- +777 2345 7885; +777 2345 7886
- 7 DAYS A WEEK FROM 10 AM TO 6 PM
Categories
- Acid 20th
- Acid Blue Label
- Acid Gold Label
- Acid Green Label
- Acid Krush Classics
- Aganorsa Leaf Aniversario 25th
- Aganorsa Leaf Arsenio
- Aganorsa Leaf Connecticut
- Aganorsa Leaf Corojo
- Aganorsa Leaf Habano
- Aganorsa Leaf Maduro
- Aganorsa Leaf Supreme Leaf
- Alec Bradley
- Amazon Basin
- Ambrosia by Drew Estate
- Aquitaine
- Arturo Fuente
- Arturo Fuente 8-5-8
- Arturo Fuente Anejo
- Arturo Fuente Chateau
- Arturo Fuente Don Carlos
- Arturo Fuente Hemingway
- Arturo Fuente Holiday Collection
- Arturo Fuente Impossible 13
- Arturo Fuente Opus X
- Arturo Fuente Opus X Tins
- Arturo Fuente Rare Pink
- Arturo Fuente RSG Magum R
- Ashton
- Atabey
- Baccarat
- Baka
- BDP
- Bentley Cigars
- Besa Cigar
- Black Star Line Cigars
- Black Star Line Lalibela
- Black Star Line War Witch
- Blackened M81 Maduro
- Blackened S84 Shade To Black
- Blue Mountain Cigars
- Bundle Of Bastards
- Cain by Oliva
- Caldwell Blind Man's Bluff
- Camacho Broadleaf
- Camacho Connecticut
- Camacho Ecuador
- Camacho Limited
- Camacho Triple Maduro
- CAO
- Casa Fuente
- Casa Magna
- Ceniza Fina
- Ceniza Fina Connecticut
- Charter Oak Broadleaf
- Charter Oak Connecticut
- Charter Oak Habano
- Chillin' Moose
- Chogui
- Cigar Hustler Private Blends
- Cohiba
- Cordoba & Morales
- CRAFT Maquette
- CroMagnon
- CroMagnon PA
- Crowned Heads
- Crowned Heads Las Calaveras
- Crowned Heads Tennessee Waltz
- Crux
- Davidoff
- Deadwood Tobacco
- Deadwood Tobacco Dominicana
- Diamond Crown
- Don Doroteo El Alcalde
- Don Doroteo El Legado
- Don Doroteo Salt of the Earth
- Dunbarton 10th Jubilee
- El Gueguense/The Wiseman
- El Rey Del Mundo
- Espinosa 601
- Espinosa Crema
- Espinosa Laranja
- Espinosa Las 6 Provincias
- Espinosa Murcielago
- Esteban Carreras
- Fable
- Factory Smokes
- Ferio Tego
- Ferio Tego Metropolitan
- Fosforo
- Fosforo Connecticut
- Fosforo Limitada
- Founders Cigar Company
- FQ Cigars
- Fuente Y Padron Legends
- Guaimaro
- Hawaiian Breeze
- Highclere Castle
- Highclere Senetjer
- Howard G Cigars
- Howard G The Perfect Round
- HVC Cerro
- HVC Hot Cake/Pan Caliente
- HVC Limited Edition
- HVC Seleccion #1
- Intemperance BA
- Intemperance EC
- Intemperance Volstead
- Intemperance WR
- Isla Del Sol
- It's A Boy / It's A Girl
- JFR
- Joya De Nicaragua
- Juarez
- Karole Bashkin
- Kentucky Fire Cured Cigars
- Knuckle Sandwich Chef's Special
- Knuckle Sandwich Connecticut
- Knuckle Sandwich Habano
- Knuckle Sandwich Maduro
- knuckle Sandwich Puro Nicaragua
- La Aroma De Cuba
- La Gloria Cubana
- La Vereda
- Leaf by Oscar
- LFD 30th
- LFD Andalusian Bull
- LFD Cabinet Series
- LFD Double Ligero
- LFD Golden Andalusian Bull
- LFD Limited
- LFD Oro
- LFD Suave
- LFD Tins
- Liga Privada Aniversario
- Liga Privada H99
- Liga Privada No. 9
- Liga Privada T52
- Liga Privada Unico Blend
- Los Statos
- Luxury Cigar Club
- Macanudo
- Maestranza
- Meerapfel
- Menelik
- Metapa Claro
- Metapa Maduro
- Mi Querida
- Mi Querida Black
- Mi Querida Triqui Traca
- Montecristo
- Montecristo White Series
- Muestra de Saka
- Neanderthal
- Nestor Miranda
- Nica Rustica
- Nub
- Oliva
- Olmec
- Oscar
- Ozgener Family Cigars
- Padron 1926 Series Maduro
- Padron 1926 Series Natural
- Padron 1964 Anniversary Maduro
- Padron 1964 Anniversary Natural
- Padron 50th Anniversary
- Padron 60th Anniversary
- Padron Black
- Padron Family Reserve
- Padron Maduro
- Padron Natural
- Padron Sampler Box Sets
- Pappy Van Winkle
- Plasencia Alma del Cielo
- Plasencia Alma del Fuego
- Plasencia Alma Fuerte
- Plasencia Alma Fuerte Natural
- Plasencia Cosecha
- Polpetta
- Ponce
- Powstanie Broadleaf
- Powstanie Connecticut
- Powstanie Habano
- Powstanie Limited
- Powstanie San Andrés
- Red Meat Lovers
- RoMa Craft Tobac
- Romeo Y Julieta
- Room101
- Room101 Daruma
- Room101 Farce
- Room101 Hit & Run
- Room101 Namakubi
- Room101 Payback
- San Cristobal
- Sextooth
- Sin Compromiso
- Sobremesa
- Sobremesa Brulee
- Sobremesa Solita
- Sobremesa Solita Red
- StillWell Star
- Tabak Dulce
- Tabak Negra
- Tatiana
- Tatuaje
- Tatuaje 7th
- Tatuaje Cojonu
- Tatuaje El Triunfador
- Tatuaje Fausto
- Tatuaje Honduras P
- Tatuaje Monsters
- Tatuaje Verocu Blue
- Ted's Cigars
- The Tabernacle
- The Tabernacle Havana Seed
- Umbagog
- Undercrown
- Undercrown 10 Year
- Undercrown Shade
- UnNamed Cigar
- Upsetters
- Viaje
- Warhead La Bomba Tin
- Weaselitos
Bestsellers
Filter By
Your IP Address is: 216.73.216.115