• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

duplicates from query

Guest
Jan 11, 2007 Jan 11, 2007

Copy link to clipboard

Copied

Hi i have an output which should show
505350 EXH-130, 150, 170, 190, 210 EXD-150, 170, 210

http://www.southerncooling.com/NEW.cfm

but i am getting duplicates, i sure i have something wrong with my query, but not sure what

can someone help
TOPICS
Advanced techniques

Views

802

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 11, 2007 Jan 11, 2007

Copy link to clipboard

Copied

What is MT.Prod_ID34?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 11, 2007 Jan 11, 2007

Copy link to clipboard

Copied

ok lets start at the beginning as i have been on this for about 3 weeks now!!!

if you look at http://www.southerncooling.com/Parts.cfm then use the selection BRAND, CATEGORY, MODEL

THE RESULTS PAGE SHOULD SHOW ALL PRODUCTS THAT MATCH THE MODEL SELECTED, AND THEN SHOW NEXT TO THE PRODUCTS THE RANGE NAME FOLLOWED BY THE MODELS NUMBERS THAT THE PRODUCT IS ACCOCIATED WITH.

IE

884443 // EA-34, 45, 56 EF-34,45,46

THEN HAVE THEM GROUPED INTO CATEGORYS FROM THE CATEGORIES TABLE

HOPE THIS MAKES SENSE



i have the following tables

**PRODUCTS_TABLE**
Products_ID (UNIQUE)
Part_No
Discription

**MANUFACTURES_TABLE**
Man_ID
Man_Name

**PRODUCT_TO_MANUFAC_TABLE**
PM_Product_ID
PM_Man_ID

**PRODUCT_TO_CATEGORY_TABLE**
PC_Product_ID
PC_Category_ID

**RANGE_TABLE**
Range_ID (UNIQUE)
Range_Name
Man_Range
Range_ID

**MODEL_TABLE**
Model_ID (UNIQUE)
Man_Model
Range_Model
Model_Name

**MODEL_PROD_TABLE**
Model_Prods_ID (UNIQUE)
Prod_ID34
Model_ID34
Range_Model
Info

and this is the full output i need

<table width="100%" border="0">
<cfoutput query="Products" group="Cat_Name">
<tr>
<td bgcolor="B02300" class="MainMidWhite"><strong>#Products.Cat_Name#</strong></td>
</tr>

<tr>
<td><table width="100%" border="0">
<tr>
<td></td>
<td bgcolor="##000000" class="fixed5"><div align="left" class="MainMidWhite"><strong>Part No </strong></div></td>
<td bgcolor="##000000"><div align="left" class="MainMidWhite"><strong>Discription</strong></div></td>
<td bgcolor="##000000" class="fixed5"> </td>
</tr>
<cfoutput>


<cfset bgcolor="##BBCCE1">

<tr>
<td width="30"></td>
<td bgcolor="#bgcolor#" class="fixed5">#Products.Part_No#</td>
<td bgcolor="#bgcolor#"><div align="left" class="Main1Black">#Products.Discription# </div></td>
<td bgcolor="#bgcolor#" class="fixed5"><div align="center"><span class="style3">#Products.Range_Name#-#Products.Model_Name#,</span> </div></td>
</tr>
</cfoutput>
</table></td>
</tr>
</cfoutput>
</table>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2007 Jan 12, 2007

Copy link to clipboard

Copied

Could you put back the query that was in the original post?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 12, 2007 Jan 12, 2007

Copy link to clipboard

Copied

o yes there you go!!



<cfquery name="Products" datasource="#application.ds#">
SELECT *
FROM Products_Table PT, category_table CT, model_prods_table MT

INNER JOIN product_to_manufact PTM
ON PT.Products_ID = PTM.PM_Product_ID

INNER JOIN product_to_category PTC
ON PT.Products_ID = PTC.PC_Product_ID

INNER JOIN manufactures_table MFT
ON MFT.Man_ID = PTM.PM_Man_ID AND MFT.Man_ID = '#session.manufac#'

INNER JOIN range_table RT
ON RT.Range_ID = MT.Range_Model

INNER JOIN model_table MMT
ON MMT.Model_ID = '#session.ModelNo#'


WHERE PT.Products_ID = MT.Prod_ID34 AND MT.Model_ID34 = '#session.ModelNo#' AND CT.Cat_ID = PTC.PC_Category_ID
GROUP BY Cat_ID
</cfquery>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 14, 2007 Jan 14, 2007

Copy link to clipboard

Copied

Your db is set up with a many to many relationship between product and category. This means a product could show up more than once, because the category is different. Is that the way it really is? Are these the duplicates you are seeing?

By the way, using "select *" might be easy to type, but it has some disadvantages. You might consider selecting only those fields you need for display or anchor tags.

Also, while your sql is correct, it would be easier to read if you made all your joins in the same clause. You have some in your from clause and some in your where clause.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 14, 2007 Jan 14, 2007

Copy link to clipboard

Copied

ok thanks Dan

yes they are the duplicates i am getting, but how would i change my db so i can do this

1. i need a products table
2. products need to be linked to categories (so i can search by category)
3. products need to linked to manufacturer (so i can search by manufacturer)
4. then i have a range and model table (these link products to a range and a model, so a product may be available for 5 differnt models or ranges)

so just not sure the best way to setup the db, thought i had done it with nomialization

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 14, 2007 Jan 14, 2007

Copy link to clipboard

Copied

Let's say that you had products and categories. A product can only belong to one category, but a category can have many products. It goes something like this:

Product table
product_id not null, primary key
category_id not null, foreign key
other fields

Category table
category_id not null, primary key
other fields

and so on and so forth. You had the right idea. Just overthought the details it seems.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 14, 2007 Jan 14, 2007

Copy link to clipboard

Copied

Ok yes i see,

but what happens when i what to link the range table and the model table

so each product may have 1 or more of a range or model

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 14, 2007 Jan 14, 2007

Copy link to clipboard

Copied

If a product can have more than one range, and a range can apply to more than one product, it's called a many to many relationship. The table structure resembles what you have now, but not for models and ranges.

Using ranges as an example, the way I like to do it is,

table product_range
product_id not null, foreign key to product
range_id not null, foreign key to range
other fields

the primary key has two fields, product_id, and range_id.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 14, 2007 Jan 14, 2007

Copy link to clipboard

Copied

ok so am i right in saying thats the table product_range as u just surgested, will be inbetween

the products table and the range table

if so i will work on it and get back to you as i know i will need some help the the query to show the results

many thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 14, 2007 Jan 14, 2007

Copy link to clipboard

Copied

try inner joins

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 14, 2007 Jan 14, 2007

Copy link to clipboard

Copied

LATEST
ok Dan i have sorted the db, now i need to show range_name and model_name next to each product

if you look at http://www.southerncooling.com/ProductResults.cfm?Model=1

the first part no 505350 should have a discription of
Tank Beige EXH-130, 150, 170, 180 EXA-130, 150, 270, 280

but instead it only shows one becaise of the session in the where clause

I have this in the product_range table but not sure how to set the query to show

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation