DallySP
asked on
sql select query
I putting together a cosmetics ecommerce site. In the database every product has a BrandID and a CatID. I then have a page for Eyeshadows which will be CatID 6, Blushers CatID 2 etc.
I also have a category called brushes and an individual brushes page. However I also want the Eyeshadow brush to appear on the eyeshadow page but am not sure what the syntax would be for the query. My current query is:
SELECT * FROM items, itembrand, itemcategory, itemstocktypes WHERE items.ItemCatID = 6 AND items.ItemCatID=itemcatego
what can I add in to tell the database to also pull up items where ItemName contains the word 'brush' AND BrandID = 1
I also have a category called brushes and an individual brushes page. However I also want the Eyeshadow brush to appear on the eyeshadow page but am not sure what the syntax would be for the query. My current query is:
SELECT * FROM items, itembrand, itemcategory, itemstocktypes WHERE items.ItemCatID = 6 AND items.ItemCatID=itemcatego
what can I add in to tell the database to also pull up items where ItemName contains the word 'brush' AND BrandID = 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi.
add the following conditions in where clause.
Items.ItemName like 'brush%' AND Items.ItemBrandID = 1
add the following conditions in where clause.
Items.ItemName like 'brush%' AND Items.ItemBrandID = 1
SELECT * FROM items, itembrand, itemcategory, itemstocktypes WHERE items.ItemCatID = 6 AND items.ItemCatID=itemcatego
jppinto
jppinto
Untested but maybe cleaner:
select * from items
join itembrand on items.ItemBrandID=itembran
join itemcategory on items.ItemCatID=itemcatego
join itemstocktypes on items.ItemStockID=itemstoc
where items.ItemCatID = 6 or ( ItemName like '%brush%') AND BrandID = 1)
order by ItemID ASC
select * from items
join itembrand on items.ItemBrandID=itembran
join itemcategory on items.ItemCatID=itemcatego
join itemstocktypes on items.ItemStockID=itemstoc
where items.ItemCatID = 6 or ( ItemName like '%brush%') AND BrandID = 1)
order by ItemID ASC
ASKER
I'm going round in circles a bit here as I realise I've got my question slightly wrong as I only want to select the brushes that relate to the specific category. I've been trying out your various suggestions and adapting but have had not joy so far - probably my adaptions are incorrect. Just to clarify therefore I want to (and excuse my syntax as I know its wrong because it doesn't work!!:
SELECT * from items WHERE items.CatID = 6 (AND items.CatID = 16 WHERE items.ItemSKU=6)
ie select all the eyeshadows and select all the brushes that relate to the eyeshadows
Hope that makes sense. CatID 6 is eyeshadows and CatID 16 is brushes and I've put a 6 in the ItemSKU field where the brush relates to eyeshadows but it doesn't work - I think I've got my brackets in the wrong place !!!
SELECT * from items WHERE items.CatID = 6 (AND items.CatID = 16 WHERE items.ItemSKU=6)
ie select all the eyeshadows and select all the brushes that relate to the eyeshadows
Hope that makes sense. CatID 6 is eyeshadows and CatID 16 is brushes and I've put a 6 in the ItemSKU field where the brush relates to eyeshadows but it doesn't work - I think I've got my brackets in the wrong place !!!
ASKER
Hi - I used something based on your second solution in the end to solve this one - thanks for your help and apologies for the delay in getting back
jppinto