1

I'm trying to build a query for fetch wall_posts and profile fields for several user_id. First see the images at: http://www.dropmocks.com/mBjQ7P. The table with id_user_rq, id_user_ap is the table where I handle the relations. Meaning 1 and 2 are friends and 1 and 3 are friends too because the directionality is 1,2 | 2,1 and 1,3 | 3,1. So what I need here is to get the wall_post for users 1,2,3 and also the profile data for the same users. Wall_posts is image #2 and profile is the first image. I've tried this query:

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid) 
    AND (default_wall_posts.userid = 2 OR default_wall_posts.userid = 3)
WHERE
    default_wall_posts.userid = 1 AND
    (
        default_wall_posts.userid = 2 AND
        default_wall_posts.userid = 3
    )
ORDER BY
    default_wall_posts.date_created DESC;

But it's not working because I only get the wall_posts and profile fields for user 1. What I'm doing wrong?

Cheers and thanks in advance

1

Here is your original query

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid) 
    AND (default_wall_posts.userid = 2 OR default_wall_posts.userid = 3)
WHERE
    default_wall_posts.userid = 1 AND
    (
        default_wall_posts.userid = 2 AND
        default_wall_posts.userid = 3
    )
ORDER BY
    default_wall_posts.date_created DESC;

The WHERE is impossible to fulfill because default_wall_posts.userid not be 1 and 2 and 3 at the same time.

Try these changes

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid) 
WHERE
    default_wall_posts.userid IN (1,2,3)
ORDER BY
    default_wall_posts.date_created DESC;
  • Thanks @rolandomysqldba you're right the WHERE will never happen because the AND maybe with a OR should work anyway your query is right and I use that instead of mine. – ReynierPM Jun 6 '12 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.