×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Query Problems - Do I need a JOIN?

thread220-1542036

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!

 

RE: Query Problems - Do I need a JOIN?

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
 

RE: Query Problems - Do I need a JOIN?

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: 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?

(OP)
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.

RE: Query Problems - Do I need a JOIN?

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.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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close