Query Problems - 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!

 

[SQLSister]

You are currently using joins you just don't know it because you are using a syntax that has been obsolete since 1992. Before you write another piece of SQL code, you need to understand joins thoroughly. Joins are not optional in a relational world.

The problem you have is that something cannot be simlutaneously be in CategoryFieldId 670 and 671. Therefore no records are retuned. You could try an OR insteand of an AND.

If that doesn't work, then what you need will be to join to the same table twice.

Here is some reading on JOIN fundametals.

http://www.tek-tips.com/faqs.cfm?fid=4785

"NOTHING is more important in a database than integrity." ESquared

 

[PHV]

What about this ?

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

I need a JOIN, which I am not familiar with
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[goodfellaNW]

SQLSister, Thanks for the kick in the butt. It's about time I pick up JOINs and get with the program. Your link is quite helpful and I've gone through some of it already, and will invest more time into it later today.

PHV, Thanks for your help as well. I've tried your suggestion:

SELECT P.ID, P.CategoryID, P.Title, P.[Description], P.Body, P.DateCreated, P.DateUpdated, P.ManufacturerID, P.ManufacturerLocationID, P.[Status]
  FROM tbl_Products P
 INNER JOIN tbl_ProductDetailsText D1 ON P.ID = D1.ProductID
 INNER JOIN tbl_ProductDetailsText D2 ON P.ID = D2.ProductID
 WHERE P.CategoryID = 87
   AND (D1.CategoryFieldID = 670 AND CAST(D1.CategoryFieldItem AS INT) BETWEEN 229 AND 231)
   AND (D2.CategoryFieldItem = 671 AND D2.CategoryFieldItem = 'NIKE')

but received the following error:

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the nvarchar value 'Nike' to data type int.

Thanks again to you both.

 

[PHV]

Sorry for the typo:

SELECT P.ID, P.CategoryID, P.Title, P.[Description], P.Body, P.DateCreated, P.DateUpdated, P.ManufacturerID, P.ManufacturerLocationID, P.[Status]
  FROM tbl_Products P
 INNER JOIN tbl_ProductDetailsText D1 ON P.ID = D1.ProductID
 INNER JOIN tbl_ProductDetailsText D2 ON P.ID = D2.ProductID
 WHERE P.CategoryID = 87
   AND (D1.CategoryFieldID = 670 AND CAST(D1.CategoryFieldItem AS INT) BETWEEN 229 AND 231)
   AND (D2.CategoryField[!]ID[/!] = 671 AND D2.CategoryFieldItem = 'NIKE')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886