0

thanks for yout time helping on this ;) I'm new to SQL and wish to solve somethign in just one query and i dont know how to do it.-

Basically I've a table of products, a table of users and a table of comments, linked by products.id -> comments.pid and user.id -> comments.uid ,

i wish to know what is the best practice to create just 1 query and get all products with child comments, including username.

DiegoMol
  • 1
  • 1
  • 1
    Do you also like to try to write your entire application in one line of code? If you need two queries, use two queries. – Mark Byers Jun 30 '12 at 08:14
  • 1
    Agree with Mark, if you think about getting all those data in one query you combine separate parts of your application. Instead it would make more sense to separate commenting from the product. That way you can work more efficiently. Next to that the situation might get more complex in future, for example you also need the categories the product is in. Just SELECT id, nam FROM products WHERE id=1 and another query: SELECT users.name, comments.comment FROM comments INNER JOIN users ON users.id=comments.uid WHERE comments.pid=1 – Luc Franken Jun 30 '12 at 09:40
  • Mark, i agree with you, to show data from one product in one page, but if you need to create a list of products like pinterest... do you think that is the best practice to loop trough products list usign multiple queries? in a heavy traffic website this could kill the server.- just think listing 100's products and getting 5 comments from each one.- there will be 101 queries, vs 1 using a join, my problem is that i dont know the limitations of joins because i am new into sql.- – DiegoMol Jun 30 '12 at 19:03

3 Answers3

4

Learn about SQL joins:

SELECT *
FROM   products
  LEFT JOIN comments ON products.id = comments.pid
  LEFT JOIN user     ON user.id     = comments.uid
eggyal
  • 122,705
  • 18
  • 212
  • 237
1

learning joins is the best way to solve such a problem

Explanation

what we are trying here to do is create a virtual table which fetches records based on some relations.

I have created three tables here

  • product
  • user
  • comments

and there is a relation set between the

  1. product and the comments table - using product.id and comments.prod_id
  2. user and the comments table - using user.id and comments.user_id

Now you can use join to filter the results

SELECT product.id,user.name,comments.DATA FROM comments LEFT JOIN product ON comments.prod_id = product.id INNER JOIN USER ON comments.user_id = USER.id;

or if you don't want to use join you can simply create a relation but please do note that using joins is the best possible way to do it

SELECT product.id,USER.name,comments.DATA 
FROM product,USER,comments
 WHERE product.id = comments.prod_id AND USER.id = comments.user_id;

you can check the results here

SqlFiddle

Ankit Suhail
  • 2,045
  • 21
  • 32
0

Istead of using JOIN you can try this,

select p.id,u.name,c.data
from products as p,user as u,comment as c
where p.id = c.pid and u.id = c.uid;
RPB
  • 16,006
  • 16
  • 55
  • 79
  • 1
    [Why isn't SQL ANSI-92 standard better adopted over ANSI-89?](http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89) – Mark Byers Jun 30 '12 at 08:19
  • 1
    Also, I suspect in this case outer joins are necessary in case a product has no comments. – eggyal Jun 30 '12 at 08:21
  • @eggyal Product which has both user associated with comment and comment only those will be displayed.. – RPB Jun 30 '12 at 08:23
  • @eggyal in question he has not mentioned that he wants all product irrespective of comment :) – RPB Jun 30 '12 at 08:26