Jump to content

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


Fishcakes
 Share

Recommended Posts

Posted (edited)

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 by Fishcakes
Link to comment
Share on other sites

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).

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.