Query Problems - Do I need a JOIN?
Query Problems - Do I need a JOIN?
(OP)
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!
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!
RE: Query Problems - Do I need a JOIN?
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
RE: Query Problems - Do I need a JOIN?
CODE
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:/
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Query Problems - Do I need a JOIN?
PHV, Thanks for your help as well. I've tried your suggestion:
CODE
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.
RE: Query Problems - Do I need a JOIN?
CODE
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.CategoryFieldID = 671 AND D2.CategoryFieldItem = 'NIKE')
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?