12 Replies Latest reply on Jan 14, 2007 7:54 PM by JohnGree

    duplicates from query

    JohnGree Level 1
      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
        • 1. Re: duplicates from query
          Dan Bracuk Level 5
          What is MT.Prod_ID34?
          • 2. duplicates from query
            JohnGree Level 1
            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>
            • 3. Re: duplicates from query
              Dan Bracuk Level 5
              Could you put back the query that was in the original post?
              • 4. duplicates from query
                JohnGree Level 1
                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>
                • 5. Re: duplicates from query
                  Dan Bracuk Level 5
                  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.
                  • 6. Re: duplicates from query
                    JohnGree Level 1
                    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
                    • 7. Re: duplicates from query
                      Dan Bracuk Level 5
                      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.
                      • 8. Re: duplicates from query
                        JohnGree Level 1
                        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

                        • 9. Re: duplicates from query
                          Dan Bracuk Level 5
                          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.
                          • 10. Re: duplicates from query
                            JohnGree Level 1
                            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
                            • 11. duplicates from query
                              RyanG24 Level 1
                              try inner joins
                              • 12. duplicates from query
                                JohnGree Level 1
                                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