Select query on multiple tables multiple times

Question

I have two tables: COMMENTS and USERS. I save user's name on COMMENTS table, but user's avatar details should be taken from USERS table.

SELECT c.id, c.tarih, c.yorum, c.yorumcu, 
(SELECT avatarID FROM USERS WHERE Username=c.yorumcu) AS avatarID 
FROM COMMENTS c WHERE c.id = ?

The question mark at the end gets replaced with NEWS_ID. (e.g news.php?id=5)

I managed to code this, but it only gets the "last comment" details from database, not all of them. (Let's say 5 people commented on page, it only gets the last (5th) comment, ignoring the rest.)

Can anybody help me?

Ps. Yorumcu means "Commenter". COMMENTS.Commenter and USERS.Username are the same.

Miika L. · Accepted Answer · 2012-01-11 08:26:46Z

2

Should your WHERE clause not be "WHERE c.news_id = ?", rather than "WHERE c.id = ?" ? I am assuming that c.id is unique to each comment.

answered Jan 11, 2012 at 8:26

Miika L.

3,3491 gold badge27 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aristona Over a year ago

I feel stupid now. Thanks alot. I was checking comment ID instead of news_id. Were about to go crazy...

dbrin · Accepted Answer · 2012-01-11 08:31:07Z

0

try this:

select c.id, c.tarih, c.yorum, c.yorumcu, avatarID 
FROM COMMENTS,USERS 
where USERS.Username=c.yorumcu  and c.id = ?

answered Jan 11, 2012 at 8:31

dbrin

15.7k4 gold badges59 silver badges85 bronze badges

1 Comment

Aristona Over a year ago

Thanks for your effort. The query above worked like a charm after I change c.id to c.news_id.

Maarten Kesselaers · Accepted Answer · 2012-01-11 08:40:38Z

0

I assume that COMMENTS.id is the unique identifier for the table COMMENTS?

If that is the case, it is logic, that you only get returned 1 comment.

Do you have a identifier for NEWSID in the COMMENTS table? Then you should use this one to retrieve multiple comments.

answered Jan 11, 2012 at 8:40

Maarten Kesselaers

1,2512 gold badges9 silver badges19 bronze badges

Collectives™ on Stack Overflow

Select query on multiple tables multiple times

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related