July 28th, 2013, 12:22 PM
Multi rows of same data
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
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
July 28th, 2013, 03:58 PM
A three turning into a nine, at first that sounds like a cross join, but...
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
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?
SELECT option_id, `name` FROM oc_option_value_description WHERE product_id = 447 AND option_id = 11
Last edited by ragax; July 28th, 2013 at 04:00 PM.
July 29th, 2013, 09:35 AM
if you sure all valus the same just add group by by the end.
So your query will be
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,
July 29th, 2013, 09:47 AM
in most cases this advice leads to masking an underlying data problem (possibly at the expense of performance)
Originally Posted by gk53
July 29th, 2013, 09:50 AM
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>
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.
July 29th, 2013, 12:21 PM
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
Originally Posted by r937