JOIN 2 tables, with 2 columns

[MasterACE14]

I have 2 tables:

= feed =
- feed_id (PK)
- user_id (FK)  (user who owns this particular record on his feed)
- poster_id (FK) (user who posted the record, can be the same as `user_id`)
- feed_message

= accounts = 
- id (PK)
- username 

 

Now I'm trying to select all records that belong to "user_id = 2" including the `username` of the `poster_id`. I've got my query selecting all the correct records, but the `username` is null for any records that don't have the same `user_id` and `poster_id`

 

Query:

SELECT `accounts`.`username`, `feed`.`feed_message` 
FROM `feed` 
LEFT JOIN `accounts` ON (accounts.id = feed.poster_id) 
WHERE feed.user_id = 2

 

Result:

username  	feed_message
ACE	 test
ACE	 test
ACE	 test
ACE	 test
ACE	 test
ACE	 test
ACE	 test
ACE	 testing
ACE	 testing
ACE	UPDATING MY FEED
ACE	 tester
NULL	test
NULL	message from player 'test'

 

Not sure where I'm going wrong?

 

Thanks,

Ace

[fenway]

Don't you mean INNER JOIN?  You want matching ones only, right?

[MasterACE14]

I'm really not sure, I've just tried an INNER JOIN and obviously it eliminates the last 2 results there. Maybe I need to use a subquery or something? I just need the `accounts`.`username`'s that match the `feed`.`poster_id`

[fenway]

Then I don't understand what you're trying to do.

[MasterACE14]

ah I've got it working with your INNER JOIN suggestion. Made a really stupid mistake, the last 2 records in my sample result set above had the wrong poster_id. My apologies. Thanks for your help!

 

Cheers, Ace