Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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;
share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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