Query syntax with MySQL

Question

I have 3 tables:

users

userid | pseudo

posts

id | titre

posts_com

I need to list the lasts posts_com with their post id owner and user owner... I tryied this query but the results are not corrects... Any idea please?

SELECT c.userid,
       c.id_billet,
       c.auteur,
       c.date,
       c.html,
       u.pseudo,
       b.titre,
       b.id
FROM posts_com AS c,
     users AS u,
     posts AS b
WHERE u.userid=b.userid
ORDER BY c.id DESC
LIMIT 12

[EDIT] I need :

-> comment 1 (html) from auteur on post titre (user foo)

-> comment 2 (html) from auteur2 on post titre2 (user foo2) ...

In what way are "the results not correct"? You appear to be missing join criteria for the posts_com table (i.e. how that table relates to the others). — eggyal
– eggyal, Commented Sep 27, 2013 at 10:31
it displays same id_billet, same date, same html and wrong titre — Recif
– Recif, Commented Sep 27, 2013 at 10:35

Shafeeque · Accepted Answer · 2013-09-27 11:07:12Z

1

Use join

SELECT A.userid,A.id_billet,A.auteur,A.date,A.html,B.pseudo,C.id,C.titre
FROM posts_com A
JOIN users B ON A.userid = B.userid
JOIN posts C ON A.id = C.id
ORDER BY A.id DESC
LIMIT 12

EDIT

SELECT A.userid,A.id_billet,A.auteur,A.date,A.html,B.pseudo,C.id,C.titre
FROM posts_com A
JOIN users B ON A.userid = B.userid
JOIN posts C ON A.id_billet = C.id
ORDER BY A.id DESC
LIMIT 12

edited Sep 27, 2013 at 11:07

answered Sep 27, 2013 at 10:39

Shafeeque

2,0692 gold badges13 silver badges28 bronze badges

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

1 Comment

Recif Over a year ago

Ok, it works with ON A.id_billet = C.id instead of ON A.id = C.id! Thanks

Thanos Darkadakis · Accepted Answer · 2013-09-27 10:34:09Z

0

line

WHERE u.userid=b.userid

There doesn't seem to be a column named userid in table posts. Maybe you mean

WHERE u.userid=b.id

?

answered Sep 27, 2013 at 10:34

Thanos Darkadakis

1,7292 gold badges18 silver badges31 bronze badges

1 Comment

Michaël Benjamin Saerens Over a year ago

I assume that the id column in post_com is the post-id, since it also has a userid column

Michaël Benjamin Saerens · Accepted Answer · 2013-09-27 10:37:06Z

0

You have not linked the post_com table to your resultset. Therefore all records of post_com are joined with all records of your userse-post join.

SELECT c.userid,
       c.id_billet,
       c.auteur,
       c.date,
       c.html,
       u.pseudo,
       b.titre,
       b.id
FROM posts_com AS c,
     users AS u,
     posts AS b
WHERE u.userid=b.userid
  AND b.id = c.id(+)
ORDER BY c.id DESC
LIMIT 12

answered Sep 27, 2013 at 10:37

Michaël Benjamin Saerens

8757 silver badges25 bronze badges

Comments

Nasir Iqbal · Accepted Answer · 2013-09-27 10:52:56Z

0

first you need to use JOINs, and 2ndly your table structure is not clear I mean there is no notable relation between posts_com and posts table, we don't know if posts_com.id = posts.id OR posts_com.userid = posts.id or posts_com.id_billet = posts.id?

SELECT c.userid,
       c.id_billet,
       c.auteur,
       c.date,
       c.html,
       u.pseudo,
       b.titre,
       b.id
FROM (posts_com c JOIN users u
       ON c.userid = u.userid
     ) JOIN posts b
       ON c.id_billet = b.id
ORDER BY c.id DESC
LIMIT 12

edited Sep 27, 2013 at 10:52

answered Sep 27, 2013 at 10:40

Nasir Iqbal

8727 silver badges24 bronze badges

1 Comment

Nasir Iqbal Over a year ago

I guess posts.id refer to id_billet in posts_com, please clear if not. answer updated

Collectives™ on Stack Overflow

Query syntax with MySQL

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related