Need Help with SQL Query. Do I need a JOIN?

[goodfellaNW]

Hello,

I have the following tables: tbl_Products, tbl_Categories, tbl_CategoryFields, tbl_ProductDetailsText

tbl_Products houses basic product data, such as ID, CategoryID, Title, Description, Body, Date, Status, etc... tbl_Categories has ID, ParentCategoryID, Title, etc...

tbl_CategoryFields has custom fields that the user can add in a CMS for specific categories. For example, Category A can have Brand, Size and Color. Where Category B would have Brand, Model Number, Color, Dimensions. Then, each product will have values in the tbl_ProductDetailsText table based on the category for which they belong to and associated with the CategoryFieldID.

tbl_CategoryFields
ID (int)
CategoryID (int)
Title (varchar)
FieldType (int)
Status (int)

tbl_ProductDetailsText
ProductID (int)
CategoryFieldID (int)
CategoryFieldItem (varchar)

The following query gives me a correct result:

SELECT 
tbl_Products.ID, tbl_Products.CategoryID, tbl_Products.Title, tbl_Products.[Description], tbl_Products.Body, tbl_Products.DateCreated, tbl_Products.DateUpdated, tbl_Products.ManufacturerID, tbl_Products.ManufacturerLocationID, tbl_Products.[Status]
FROM 
tbl_Products, tbl_ProductDetailsText
WHERE
tbl_Products.ID = tbl_ProductDetailsText.ProductID AND
tbl_Products.CategoryID = 87 AND (tbl_ProductDetailsText.CategoryFieldID = 670 AND CAST(tbl_ProductDetailsText.CategoryFieldItem AS INT) >= 0 AND CAST(tbl_ProductDetailsText.CategoryFieldItem AS INT) <= 999)

However, when I want to add in another ProductDetailsText (i.e Size & Brand), I get no results, even though they exist:

SELECT 
tbl_Products.ID, tbl_Products.CategoryID, tbl_Products.Title, tbl_Products.[Description], tbl_Products.Body, tbl_Products.DateCreated, tbl_Products.DateUpdated, tbl_Products.ManufacturerID, tbl_Products.ManufacturerLocationID, tbl_Products.[Status]
FROM 
tbl_Products, tbl_ProductDetailsText
WHERE
tbl_Products.ID = tbl_ProductDetailsText.ProductID AND
tbl_Products.CategoryID = 87 AND (tbl_ProductDetailsText.CategoryFieldID = 670 AND CAST(tbl_ProductDetailsText.CategoryFieldItem AS INT) >= 0 AND CAST(tbl_ProductDetailsText.CategoryFieldItem AS INT) <= 999) AND
(tbl_ProductDetailsText.CategoryFieldID = 671 AND tbl_ProductDetailsText.CategoryFieldItem = 'Nike')

Nike products with a size between 0 and 999 exist in the database, but nothing is being retrieved. To me it seems as though something fundamental is wrong or I need a JOIN, which I am not familiar with.

Your help would be appreciated.

Thank you!

[extofer]

Try joining the tables:

[B]FROM[/B]
tbl_Products [B]INNER JOIN[/B]  tbl_ProductDetailsText
[B]ON [/B]tbl_Products.ID = tbl_ProductDetailsText.ProductID

[B]WHERE[/B]
tbl_Products.CategoryID = 87 AND (tbl_ProductDetailsText.CategoryFieldID = 670 AND CAST(tbl_ProductDetailsText.CategoryFieldItem AS INT) >= 0 AND CAST(tbl_ProductDetailsText.CategoryFieldItem AS INT) <= 999)

[saurav.prasad28]

HI try this

SELECT 
tbl_Products.ID, tbl_Products.CategoryID, tbl_Products.Title, tbl_Products.[Description], tbl_Products.Body, tbl_Products.DateCreated, tbl_Products.DateUpdated, tbl_Products.ManufacturerID, tbl_Products.ManufacturerLocationID, tbl_Products.[Status]
FROM 
tbl_Products JOIN  tbl_ProductDetailsText
ON tbl_Products.ID = tbl_ProductDetailsText.ProductID 
WHERE
tbl_Products.CategoryID = 87 AND (tbl_ProductDetailsText.CategoryFieldID = 670 AND CAST(tbl_ProductDetailsText.CategoryFieldItem AS INT) >= 0 AND CAST(tbl_ProductDetailsText.CategoryFieldItem AS INT) <= 999) AND
(tbl_ProductDetailsText.CategoryFieldID = 671 AND tbl_ProductDetailsText.CategoryFieldItem = 'Nike')