Re: SQL Join query

(Sorry about all the quoting but I thought it makes sense for clarity - Mattias) Curt Zirzow wrote:

* Thus wrote Lenar Lhmus:

Mattias Thorslund wrote:

Though, (regardless of SQL database platform) in a case like this, it MAY be both more straightforward and efficient to use two separate queries for the pictures and comments, given the one-to-many cardinality between them. The above statement causes redundant data to be returned - it duplicates the pics record for each new comment. If there are many large columns in the pics table and many comments to each record there's a lot of redundant data in a query like that.

No, I think you are wrong. There was GROUP BY pics.id so it won't return duplicate data. And probable overhead is eliminated too I think - DBserver will optimize and will join data from pic_comments only once for each unique pics.id.

The results will be something like: id submitter count pic_id comment

1  joe        4      1    comment1
1  joe        4      1    comment2
1  joe        4      1    comment3
1  joe        4      1    comment4
2  john       0      2    NULL
3  jim        5      3    comment6
3  jim        5      3    comment7
3  jim        5      3    comment8
3  jim        5      3    comment9
3  jim        5      3    comment10

Exactly. To make it REALLY obvious, I'll point out what parts of the data set that are redundant by replacing them with stars, using Curt's example above:

id submitter count pic_id comment

1   joe         4     1       comment1
*   *           *     *       comment2
*   *           *     *       comment3
*   *           *     *       comment4
2   john        0     2       NULL
3   jim         5     3       comment6
*   *           *     *       comment7
*   *           *     *       comment8
*   *           *     *       comment9
*   *           *     *       comment10

The actual table structure from the original poster (an ex-namesake of mine*) would result in seven columns with redundant data, i.e. where data is needlessly repeated for every picture that has two or more comments.. It wouldn't be hard to find worse real-world examples. pics table: | id | submitter | time | date | category | pic_comments table: | pic_id | comment | - Mattias Thorslund (*) My last name used to be Jönsson, too. Americans just can't pronounce it right.