Mysql query in mysql query?

[dlouis95]

Hello. I have two tables in a databse, friends and posts. Im trying to select all the posts by my unique number of friends and display them in order all in one mysql query. Is this posible?

 

I know this wouldnt work, but i was thinking it could be something like:

SELECT * FROM posts WHERE id ='(SELECT id FROM friends WHERE friend = $me)'

 

How can i make something like this?

[dlouis95]

I'm using MySQL client version: 5.1.48 

[Jumpy09]

SELECT * FROM posts as p, friends as f WHERE f.friend = '" . $me  . "' AND f.main = p.id

 

Where I have '" . $me . "', I suppose you could continue doing just $me.  Although I prefer my way, either way works.

 

There is a downfall to doing this, you need to specify which fields you want to pull from.

 

p.poster, p.message, p.date, and so forth.

 

This will pull all the information from posts depending on whether you are friends with the person who posted them, if you are not it won't pull them.  I would suggest using "ORDER BY p.date DESC LIMIT 10" or so.

 

Example:

SELECT p.poster, p.message, p.date FROM posts as p, friends as f WHERE f.friend = '" . $me  . "' AND f.main = p.id ORDER BY p.date DESC LIMIT 10

 

I hope that's what you were aiming for.

[dlouis95]

SELECT * FROM posts as p, friends as f WHERE f.friend = '" . $me  . "' AND f.main = p.id

 

Where I have '" . $me . "', I suppose you could continue doing just $me.  Although I prefer my way, either way works.

 

There is a downfall to doing this, you need to specify which fields you want to pull from.

 

p.poster, p.message, p.date, and so forth.

 

This will pull all the information from posts depending on whether you are friends with the person who posted them, if you are not it won't pull them.  I would suggest using "ORDER BY p.date DESC LIMIT 10" or so.

 

Example:

SELECT p.poster, p.message, p.date FROM posts as p, friends as f WHERE f.friend = '" . $me  . "' AND f.main = p.id ORDER BY p.date DESC LIMIT 10

 

I hope that's what you were aiming for.

 

Youve been a huge help but im still haveing alittle problem with this. Can you help me make the following script work?

 

SELECT p.user_id, p.post, p.time FROM posts as p, friend as f WHERE f.user_id = '$user_id' AND p.user_id = 'f.friend_id' ORDER BY p.time DESC LIMIT 10

 

It all works up until the p.user_id = 'f.friend_id' part. Know how to solve this problem? Thanks for the help in advance.

[dlouis95]

Ive tryed a new method:

 

"SELECT * FROM `posts` WHERE `user_id`=(SELECT `friend_id` FROM `friend` WHERE user_id = '$user_id')"

 

But this gave me the error that I was selecting more than one row, which I was and need to do. Can anyone help me in trying to make this work? Not just this script but can any one help me in trying to accomplish my goal?

[fenway]

Use IN, not -.

[Jumpy09]

SELECT p.user_id, p.post, p.time FROM posts as p, friend as f WHERE f.user_id = '$user_id' AND p.user_id = 'f.friend_id' ORDER BY p.time DESC LIMIT 10

 

Well since I posted this last I've started working on a new method.  It works for what I need it to.

 

SELECT p.user_id, p.post, p.time
FROM friend AS f
LEFT JOIN posts AS p ON p.user_id = 'f.friend_id'
WHERE f.user_id = '" . $user_id . "'
ORDER BY p.time DESC
LIMIT 10

 

Now if I have this right, it will pick up the Left Field first, which would be Friend.  If friend has a valid ID then it'll pull from posts.

 

You could even pull the user information if you wanted to and display: "" $user has no posts. "" because it would pull the Friend's Information whether or not that friend has a post.

 

I found it a bit easier to write out my queries like this than the way I was doing it before.

 

If you want you can just use JOIN, which would make it act like it was doing before.