#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    1
    Rep Power
    0

    Multi rows of same data


    Code:
    SELECT oc_option_value_description.option_id, oc_option_value_description.name, oc_product_option_value.quantity FROM oc_option_value_description LEFT JOIN oc_product_option_value ON oc_option_value_description.option_id = oc_product_option_value.option_id WHERE product_id = 447 AND oc_option_value_description.option_id = 11
    Hi

    I haven't posted all the table data just yet as this seems it could be a simple fix.

    I am quite new to mysql/php but am trying to learn more technical code.

    I run the above sql code and it should return 3 rows, i know this for a fact, but it actually returns 9, it returns 3 of each row.

    Is it just something im missing off? i have googled but can seem to run into an article or post about this error.

    if you need to see my database structure im happy to post it.

    Kind regards and many thanks in advanced

    cheers

    jamie
  2. #2
  3. Turn left at the third duck
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Location
    Nelson, NZ
    Posts
    112
    Rep Power
    93
    Hi Jamie,

    A three turning into a nine, at first that sounds like a cross join, but...

    it should return 3 rows, i know this for a fact
    The one sure thing is that the query returns exactly what it should.

    Hard to tell just like that, but a few questions / observations to move things along.
    - Which table does product_id (from the WHERE condition) come from?
    - A minor syntax tip: if option_id is a foreign key in the value_description table, your ON clause can be written more compactly with USING:
    [LEFT] JOIN... USING (option_id)
    - Are you sure you need a LEFT join, and not a plain join?
    - What makes you say that the query should only return three rows? If you run a simpler query just on the table on the left side of the join, how many rows do you get?

    For instance, assuming product_id is part of the LEFT table, what happens with

    Code:
    SELECT option_id, `name`    FROM   oc_option_value_description  WHERE product_id = 447 AND option_id = 11
    Oh, and it might have nothing to do with the SQL, but with how you are observing the results. Where are you observing the results? In a database management program? As the output of your application code?
    Last edited by ragax; July 28th, 2013 at 04:00 PM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    if you sure all valus the same just add group by by the end.
    So your query will be
    Code:
    SELECT oc_option_value_description.option_id, 
    oc_option_value_description.name, 
    oc_product_option_value.quantity 
    FROM oc_option_value_description 
    LEFT JOIN oc_product_option_value 
    ON oc_option_value_description.option_id = oc_product_option_value.option_id 
    WHERE product_id = 447 AND oc_option_value_description.option_id = 11
    group by oc_option_value_description.option_id, 
    oc_option_value_description.name, 
    oc_product_option_value.quantity
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by gk53
    "just add group by by the end"
    in most cases this advice leads to masking an underlying data problem (possibly at the expense of performance)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    10
    Rep Power
    0

    Cartesian Product, dupicate data, incomplete natural key


    There could potentially be a few things wrong. You could either have duplicate data in your tables.

    SELECT DISTINCT <columns>
    FROM
    JOIN
    ON
    WHERE

    or

    SELECT <columns>
    FROM
    JOIN
    ON
    WHERE
    GROUP BY

    If you do not have duplicates in your data then you are joining on portions of the natural key instead of a surrogate foreign key, creating a Cartesian product.

    A surrogate key is usually system generated and is a single column to join on. The surrogate key contains no duplicates. The other way that a table should be able to select a unique row is the natural key. This is made up of one or many columns that together always get a unique record.

    I suggest always joining on the surrogate key when doing one-to-one relationships. Joining on portions of the natural key are useful when performing aggregate functions on columns that are not a part of the natural key.

    A Cartesian product is when you perform a join on some but not all of the natural key which then scales out your results by having partial matches that you didn't want.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    Originally Posted by r937
    in most cases this advice leads to masking an underlying data problem (possibly at the expense of performance)
    I agree, to link data one to one and do not have duplicates is better, but to do that is not enough information and user asking for quick help... Why not just add group by??? Performance? Is he has huge tables? I'm sure not

IMN logo majestic logo threadwatch logo seochat tools logo