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

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0

    Question Distinct results from tbl 1 matched to multi results tbl 2


    Thanks for any help in advance.

    What I'm trying to do is show a listing of books from tbl 1 and a listing of reviews from tbl 2. Right now I'm getting a distinct list from tbl 1 but only one comment from tbl 2 but I want to group multi comments from tbl 2 to each item from tbl 1 but the answer is eluding me.

    Tbl 1 has a id for each book tbl 2 has a id plus the id from tbl 1.

    I'm using eruby to query mysql so far my SQL code looks like this.

    Code:
    ("select DISTINCT book.id, book.title, book.author, book.published, book.rating, review.username, review.comment
    from book, review
    where book.id = review.book_id 
    order by review.id desc")
    while row=sth.fetch do
    id = "#{row[0]}"; title = "#{row[1]}"; author = "#{row[2]}"; published = "#{row[3]}"; rating = "#{row[4]}"; username = "#{row[5]}"; comment = "#{row[6]}"
    <tr>
    <td><img src="/books/<%=id%>.png" alt="<%=title%>"></td><td><b>Author: <%=author%> | Title: <%=title%> | Published: <%=published%> | Review by: <%=username%></b><p><i><%=comment%></i></p></div></td>
    </tr>
    sth.finish
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    your query is fine, and you should remove the DISTINCT, it's unnecessary overhead

    i might sort the data by review within book, rather than review overall

    if you want also to see books that have no reviews, you must use a LEFT OUTER JOIN
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0
    Thanks for the reply.

    The problem is without the DISTINCT the same book get listed again for every review instead of getting the reviews grouped by book. I'm looking for 1 book with many reviews.

    If I use group by book.id I get the individual book but if there is more then one review it does not show the other reviews.

    I know I'm missing something really simple.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    Originally Posted by Peter_Parker
    I know I'm missing something really simple.
    yes... the fact that a one-to-many relationship ~must~ return the values from the "one" table for each set of values from the "many" table, because the result set has to be an actual table of rows and columns

    B1 R1
    B1 R2
    B1 R3
    B2 R1
    B2 R2
    B3 R1

    the book columns ~have~ to repeat for each review

    now, printing them is a different story... you might want to print the results like this --

    B1
    - R1
    - R2
    - R3
    B2
    - R1
    - R2
    B3
    - R1

    however, this is not what you should expect from sql
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0
    So I have a select that does what I need but I need a second step to display the results in a meaningful way.

    you have me pegged. I'm over my head but I am learning and the hint was helpful.

    I will dig into this more. I now have a few ideas.

    Thanks,
    Peter

IMN logo majestic logo threadwatch logo seochat tools logo