CODEWITHSUNDEEP
mysqljoin
Marcin
Marcin

Reputation: 105

How can I combine four queries into one query?

Structure of my tables:

posts (id, name, user_id, about, time)    
comments (id, post_id, user_id, text, time)
users_votes (id, user, post_id, time)
users_favs ( id, user_id, post_id, time)

How can I combine these four queries (not with UNION):

SELECT `id`, `name`, `user_id`, `time` FROM `posts` WHERE `user_id` = 1
SELECT `post_id`, `user_id`, `text`, `time` FROM `comments` WHERE `user_id` = 1
SELECT `user`, `post_id`, `time` FROM `users_votes` WHERE `user` = 1
SELECT `user_id`, `post_id`, `time` FROM `users_favs` WHERE `user_id` = 1

Should I use JOINs?

What would the SQL query for this be?

Upvotes: 0

Views: 91

Answers (1)

tadman
tadman

Reputation: 211540

You don't want to join these together.

The kind of JOIN you'd use to retrieve this would end up doing a cross-product of all the rows it finds. This means that if you had 4 posts, 2 comments, 3 votes, and 6 favorites you'd get 4*2*3*6 rows in your results instead of 4+2+3+6 when doing separate queries.

The only time you'd want to JOIN is when the two things are intrinsically related. That is, you want to retrieve the posts associated with a favorite, a vote, or a comment.

Based on your example, there's no such commonality in these things.

Upvotes: 2

Related Questions