Query Problems - Do I need a JOIN?

goodfellaNW · Apr 9, 2009

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 · Apr 9, 2009

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 · Apr 9, 2009

What about this ?

Code:

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 · Apr 9, 2009

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:

Code:

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 · Apr 9, 2009

Sorry for the typo:

Code:

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Query Problems - Do I need a JOIN?

goodfellaNW

Programmer

SQLSister

Programmer

PHV

MIS

goodfellaNW

Programmer

PHV

MIS

Similar threads

Part and Inventory Search

Sponsor