Unsure why I cannot see the table i'm doing a join on

[Fishcakes]

Hi

I'm trying to figure out why this statement produces the below

select Posts.id as PostsId, 
 	Posts.User as PostsUser, 
	CommentText,IdOfThread,
	ParentId from Posts 
	join Users on Users.User=Posts.User
	where Posts.IdOfThread = '281'; 

| PostsId | PostsUser | CommentText                                                                                                                                                                                                                                                                                                                                                                                                   | IdOfThread | ParentId |
+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------+
|     136 | JoeySteel |                                                                                                                                                                                                                                                                                                                                                                                                               |        281 |     NULL |
|     137 | JoeySteel | test                                                                                                                                                                                                                                                                                                                                                                                                          |        281 |     NULL |
|     138 | JoeySteel | Test Comment Child                                                                                                                                                                                                                                                                                                                                                                                            |        281 |      136 |
|     139 | JoeySteel | Test Comment Child                                                                                                                                                                                                                                                                                                                                                                                            |        281 |      136 |
|     140 | JoeySteel | <img src="https://lemmygrad.ml/pictrs/image/5aqjdOcJ15.jpg" />                                                                                                                                                                                                                                                                                                                                                |        281 |     NULL |
|     141 | JoeySteel | He’s pretty understated. He had some funny observations about people who we put to work that had no prior experience and just was all “it was an uphill battle but they knew what they were doing eventually and worked just as well as any other specialist.” Him complaining about the lack of taxis was pretty funny too. Also gesticulating as communication when his escorts weren’t available.          |        281 |     NULL |
+---------+-----------+-----

Which is basically all the info I want. However I also need the avatar column from the Users table.

 

However when I modify the statement to include "Users.avatar"  it returns that it doesn't exist

select Posts.id as PostsId, Posts.User 
	as PostsUser, CommentText,IdOfThread, ParentId 
    from Posts 
    join Users on Users.User=Posts.User,Users.avatar 
    where Posts.IdOfThread = '281'; 
ERROR 1146 (42S02): Table 'Users.avatar' doesn't exist

However you can see that Users.avatar does exist

describe Users ; 
+----------+-----------------+------+-----+---------------------+-------------------------------+
| Field    | Type            | Null | Key | Default             | Extra                         |
+----------+-----------------+------+-----+---------------------+-------------------------------+
| id       | int(6) unsigned | NO   | PRI | NULL                | auto_increment                |
| User     | varchar(30)     | NO   |     | NULL                |                               |
| Password | varchar(50)     | YES  |     | NULL                |                               |
| DateReg  | timestamp       | NO   |     | current_timestamp() | on update current_timestamp() |
| avatar   | varchar(150)    | YES  |     | NULL                |                               |
+----------+-----------------+------+-----+---------------------+-------------------------------+

I have tried using the left and right joins (which didn't seem to make a difference) as well as "full outer join" (Mariadb didn't recognise that)

 

Thanks

Edited May 24, 2021 by Fishcakes

[Barand]

6 hours ago, Fishcakes said:

However I also need the avatar column from the Users table.

Then add "avatar" to the list of columns you are selecting (SELECT clause). You have added it to the tables to select from (FROM clause).

[Fishcakes]

Thanks yeah I managed to work out you could select just the one column like so

select Posts.*, Users.avatar from Posts

 

[Barand]

You could, but using * in a select is rarely a good idea.