Clever Geek Handbook

Error reaching results in relational database

I have a table setup as following

enter image description here

I am trying to write one request to return a result when status = 1 is confirmed = 1 and user id = specified. I want the result to display the user id, the name associated with that user id, the freind id, and the name associated with that freind id. So far following online tutorials I came up with the following query

SELECT `freinds.User ID`, `freinds.Freind ID`, `users.User`, `users.User` FROM `freinds` WHERE `User ID`= 6 INNER JOIN `users` on `users.ID`=6 AND `users.ID`=`freinds.Freind ID`

However, I am getting sql error with this query. I think what I should do is Inner join the user table twice, but I'm not really sure how to do this.

+3


source to share


1 answer


the problem is that you are swapping the table name and backspace column name altogether. you need to separate it, or if the name is not in the MySQL `reserved keywords, remove the backlinks.

You also need to join the table twice User

so you can get the names for each id in the table Freinds

.

SELECT  a.`User ID`, 
        a.`Freind ID`, 
        b.User FriendName,
        c.User UserName
FROM    `freinds` a
        INNER JOIN `users` b 
            ON b.ID = a.`Freind ID`
        INNER JOIN users c
            ON c.id = a.`user ID`
WHERE   `User ID`= 6


As a side element, if one of ID

is null LEFT JOIN

is preferred than INNER JOIN

.

For full information on joins, kindly visit the link below:

+2


source






More articles:


All Articles