MS SQL

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

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Apr 2009

Posts: 1

Reputation: goodfellaNW is an unknown quantity at this point

Solved Threads: 0

goodfellaNW goodfellaNW is offline

Offline

Newbie Poster

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

Apr 9th, 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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)
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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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')
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!

•

Join Date: Aug 2005

Posts: 239

Reputation: extofer is an unknown quantity at this point

Solved Threads: 5

extofer

Offline

Posting Whiz in Training

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

Apr 9th, 2009

Try joining the tables:

Help with Code Tags

(Toggle Plain Text)

FROM
tbl_Products INNER 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)

Last edited by peter_budo; Apr 10th, 2009 at 10:06 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Apr 2009

Posts: 11

Reputation: saurav.prasad28 is an unknown quantity at this point

Solved Threads: 0

saurav.prasad28 saurav.prasad28 is offline

Offline

Newbie Poster

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

Apr 16th, 2009

HI try this

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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')
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')

Last edited by peter_budo; Apr 18th, 2009 at 4:06 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.