Database Discussion - Tough SQL Query Problem

Print Page | Close Window

Tough SQL Query Problem

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=12467
Printed Date: 30 March 2026 at 7:27am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Tough SQL Query Problem

Posted By: erika1959
Subject: Tough SQL Query Problem
Date Posted: 07 November 2004 at 5:16am

I've just started learning SQL in order to manipulate my databases, but the gargantuan nature of the following problem has obscured my ability to think striaght, so that I don't really have any clear idea about what kind of query I need to write in order to solve it!

I have 2 tables;
- sts-catalogue, which is a catalogue of products. It has fields as follows;

Product Number
Product Name
Price
Short Description
Category 1
Category 2
Category 3
Picture
Add to Cart Link
Thumbnail.

- ratings-review, which is a database of user ratings and reviews for those products. Its fields are;

Product Number
Rating
Rating Link
Star Rating
Comments

I want to join/merge these two tables. However there are a couple of complications;

- Not every product listed in sts-catalogue has a corresponding record in ratings-review (although all records in ratings-review are present in sts-catalogue).

- With the table ratings-review, there are many occurences of more than one user review for a single product, so that the same Product Number and Rating Link appears in several records, even though the Rating,Star Rating and Comments fields for each record is different.

Because I want to produce web pages individual products, which will include all the reviews for a particular product appearing on the same page, I'm going to need to have individual records which will include a field for each series of ratings.

Thus my final joined table format will need to look something like this:

Product Number
Product Name
Price
Short Description
Category 1
Category
Category 3
Picture
Add to Cart Link
Thumbnail
Rating Link
Rating1
Star Rating1
Comments1
Rating2
Star Rating2
Comments2
Rating3
Star Rating3
Comments3(...and so on)

It might appear that I'm simply getting someone to do my dirty work for me here, but I'm not being lazy - I tend to take solutions and play with them in different situations, which expands my ability to think for myself, so any help offered will be greatly appreciated.

Replies:

Posted By: ljamal
Date Posted: 07 November 2004 at 10:29am

You're not going to be able to do that. You can product additional ROWS with the ratings comments, but not additional COLUMNS.

Do a JOINon the two tables and then move through the record set to display all the reviews.

If you are using MS SQL Server, then my suggestion would be to use a stored procedure and return one record with the product info and one recordset with the ratings information.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: MadDog
Date Posted: 07 November 2004 at 12:05pm

This will join the tables together even if the rattings table is empty:

SELECT sts-catalogue.* FROM sts-catalogue LEFT JOIN ratings-review ON sts-catalogue.product_number =  ratings-review.product_number;

*UNTESTED*

-------------
http://www.iportalx.net" rel="nofollow">