sql syntax help

[sasa]

SELECT i.ItemId, i.Name, f.Name
From item i, features f, itemfeatures itf
Where i.ItemId = itf.ItemId
AND f.FeatureId = itf.FeatureId 
AND f.Name = 'Sugar Free'
OR f.Name = 'Chocolate'

change to

SELECT i.ItemId, i.Name, f.Name
From item i, features f, itemfeatures itf
Where i.ItemId = itf.ItemId
AND f.FeatureId = itf.FeatureId 
AND (f.Name = 'Sugar Free' OR f.Name = 'Chocolate')

[samoht]

Thanks for the help.

 

I still don't think this is exactly what I need because this returns all the records for both field names - but I only want the records that have both field names. The current db has only 41 'Sugar Free' items - so my query should return less than that.

[lemmin]

Wait, why would it return less than 41? Aren't you trying to return ones that have either 'Sugar Free' OR 'Chocolate' in the Name? Wouldn't that be 41 plus the number of ones with 'Chocolate'? If not, what exactly are you trying to do?

[samoht]

No, I want only those items that are both sugar free and chocolate.

 

I do not want all the sugar free and all the chocolate items. You see I have a top level nav that shows "Chocolate" | "Hard Candy | etc

under these I have a sub level nav that contains several options - one of which is "Sugar Free". So there is Sugar Free Chocolate and there is Sugar Free Hard Candy and so on. When I pick Sugar Free under the Chocolate top nav I only want sugar free chocolate items (not sugar free what ever - nor chocolate what ever)

 

Hopefully that makes sense

 

[lemmin]

But 'Chocolate' and 'Sugar Free' are in the same field? That doesn't make sense. Hopefully they aren't, in which case, what are the field names that 'Chocolate' and 'Sugar Free' would be in.

[samoht]

After fooling around a lot and asking every one and getting all kinds of answers -

I may have a solution:

 

<?php
SELECT i.Name AS ItemName, pd.ItemId as Id, pp.ProductId, pd.Name, pd.PriceRetail, pp.PriceSell, i.ImagetnURL, QtyInStock, pack.PackageType, s.Name as Size
FROM product pd, item i, productprice pp, packaging pack, size s, brands b
WHERE i.ItemId IN 
(
SELECT ItemId 
FROM features f
INNER JOIN itemfeatures itf ON f.FeatureId = itf.FeatureId 
WHERE f.Name = 'Sugar Free'
)
AND i.ItemId IN 
(
SELECT ItemId 
FROM features f
INNER JOIN itemfeatures itf ON f.FeatureId = itf.FeatureId
WHERE itf.FeatureId = 'Chocolate'
) 
AND pd.PackageId = pack.PackageId
AND i.BrandId = b.BrandId
AND pd.SizeId = s.SizeId
AND pd.ItemId = i.ItemId
AND pd.ProductId = pp.ProductId
AND ClientPriceCode = 'R1'";

At least it seems to be on the right track - and maybe now I can sleep at night

 

does this make sense?

[lemmin]

I really don't know how your tables are set up so it is hard to make sense of it, but if it works, good work. One thing that contradicts what I had previously though about your tables, is itf.FeatureId a string?

[samoht]

ooppss,

 

No you were right FeatureId is an int not text

it should be:

WHERE i.ItemId IN 
(
SELECT ItemId 
FROM features f
INNER JOIN itemfeatures itf ON f.FeatureId = itf.FeatureId 
WHERE f.Name = 'Sugar Free'
)
AND i.ItemId IN 
(
SELECT ItemId 
FROM features f
INNER JOIN itemfeatures itf ON f.FeatureId = itf.FeatureId
WHERE f.Name = 'Chocolate'
)

[lemmin]

So does it work?

 

I just don't understand one thing; How is 'Sugar Free' and 'Chocolate' in the same field if one is a sub-category of the other? Just to be clear, they are in the same field? If this were the case, no row would be able to be 'Chocolate' and 'Sugar Free' at the same time. There has got to be something I am missing. Do you think you could post an example row with the field names of an entry that you want it to return?

[samoht]

yes it can be a little confusing when you think of it from the stand point of categories.

 

My boss wanted to be able to store items in multiple categories - but also have a way of organizing the categories. So our db has categorygroups and categories - Chocolate is a categorygroup to which sugar free is a category.

 

The way we decided to best enable us to navigate through the db was by feature. - so we created a features table and an itemfeatures table  - each categorygroup and category has a matching feature - and any item can have multiple features - so that an Item (say Almond Joy) can have the features of Chocolate, Candy Bar, Almond, Coconut etc

 

an example output with out the feature name:

ItemNameItemIdProductIdProductNamePriceQtyInStock

Sugar Free Chocolate Bridge Mix1535 9217  Sugar Free Chocolate Bridge Mix 7.209999

Chocolate Pretzels Mini 1155 10120 Chocolate Pretzels Mini - Sugar Free - 5 Oz. Bags 1.16 9999

 

with the feature name:

ItemNameItemIdProductIdProductNamePriceQtyInStockfeature

Sugar Free Chocolate Bridge Mix1535 9217  Sugar Free Chocolate Bridge Mix 7.209999Chocolate

Sugar Free Chocolate Bridge Mix1535 9217  Sugar Free Chocolate Bridge Mix 7.209999Sugar Free

Chocolate Pretzels Mini 1155 10120 Chocolate Pretzels Mini - Sugar Free - 5 Oz. Bags 1.16 9999Chocolate

Chocolate Pretzels Mini 1155 10120 Chocolate Pretzels Mini - Sugar Free - 5 Oz. Bags 1.16 9999Sugar Free

[lemmin]

Oh. You are looking for a sub string inside of a field. The = operator compares the whole thing. You need to use LIKE.

"WHERE f.name LIKE '%Sugar Free%' AND f.name LIKE '%Chocolate%'"

 

That should return results where the field has both of thos substrings in it.