Why do I have to use DISTINCT for this to work?

Question

Hi, here's my problem: I have an SQL query that makes 4 calls to a lookup table to return their values from a list of combinations in another table. I finally got this working, and for some reason, when I run the query without DISTINCT, I get a ton of data back, so I'm guessing that I'm either missing something or not doing this correctly. It would be really great if this would not only work, but also return the list alphabetically by the first colour name. I'm putting my SQL here I hope I've explained this well enough:

SELECT DISTINCT 
    colour1.ColourID AS colour1_ColourID, 
    colour1.ColourName AS colour1_ColourName, 
    colour1.ColourHex AS colour1_ColourHex, 
    colour1.ManufacturerColourID AS colour1_ManufacturerColourID, 
    colour2.ColourID AS colour2_ColourID, 
    colour2.ColourName AS colour2_ColourName, 
    colour2.ColourHex AS colour2_ColourHex, 
    colour2.QEColourID2 AS colour2_QEColourID2, 
    colour3.ColourID AS colour3_ColourID, 
    colour3.ColourName AS colour3_ColourName, 
    colour3.ColourHex AS colour3_ColourHex, 
    colour3.QEColourID3 AS colour3_QEColourID3, 
    colour4.ColourID AS colour4_ColourID, 
    colour4.ColourName AS colour4_ColourName, 
    colour4.ColourHex AS colour4_ColourHex, 
    colour4.QEColourID4 AS colour4_QEColourID4, 
    Combinations.ID, 
    Combinations.ManufacturerColourID AS Combinations_ManufacturerColourID, 
    Combinations.QEColourID2 AS Combinations_QEColourID2, 
    Combinations.QEColourID3 AS Combinations_QEColourID3, 
    Combinations.QEColourID4 AS Combinations_QEColourID4, 
    Combinations.ColourSupplierID, 
    ColourSuppliers.ColourSupplier 
FROM 
    ColourSuppliers INNER JOIN 
    (
        colour4 INNER JOIN 
        (
            colour3 INNER JOIN 
            (
                colour2 INNER JOIN 
                (
                    colour1 INNER JOIN Combinations ON 
                     colour1.ColourID=Combinations.ManufacturerColourID
                ) ON colour2.ColourID=Combinations.QEColourID2
            ) ON colour3.ColourID=Combinations.QEColourID3
        ) ON colour4.ColourID=Combinations.QEColourID4
    ) ON ColourSuppliers.ColourSupplierID=Combinations.ColourSupplierID 
WHERE Combinations.ColourSupplierID = ?

Thanks Steph

Answer 1

My guess (and it is a guess because your SQL query is very wide!) is that you're getting the cartesian product.

Answer 2

It looks as though you've probably got multiple records for each set of four colour combinations in the Combinations table - posting the structure of the table might help us to work it out.

Adding the clause order by colour1.ColourName to the end of the query should sort it alphabetically by the first colour name.