开发者

MySQL - Mapping user username to user's ID in a query

I've done some complex SQL queries, but never joined two tables by multiple values, and im not sure is it is possible.

I've the following two tables:

        users
        +-------+----------+
        | u_ID  | username |
        +-------+----------+
        |   1   |  Pablo   |
        +-------+----------+
        |   2   |   Mike   |
        +-------+----------+


        Messages
        +-------+----------+---------+
        | m_ID  | auhor_id | message |
        +-------+----------+---------+
        |   1   |     2    | Heelo5  |
        +-------+----------+---------+
        |   2   |     1    | he4532  |
        +-------+----------+---------+
        |   3   |     2    | he4111  |
        +-------+----------+---------+
        |   4   |     1    | he4538  |
        +-------+----------+---------+

What SQL query will i need to get this table?

    +-------+----------+---------+
    | m_ID  | username | message |
    +-------+----------+---------+
    |   1   |   Mike   | Heelo5  |
    +-------+-开发者_JAVA技巧---------+---------+
    |   2   |  Pablo   | he4532  |
    +-------+----------+---------+
    |   3   |   Mike   | he4111  |
    +-------+----------+---------+
    |   4   |  Pablo   | he4538  |
    +-------+----------+---------+


select m.m_ID, u.username, m.message
from Messages m, users u
where m. auhor_id = u.m_ID;

Probably you want additional conditions in the WHERE clause to specify which Messages records. As I've written it, all messages records are displayed.


select
 m.m_ID,
 u.username,
 m.message
from
 users u
inner join messages m on u.u_ID = m.author_id
order by
 m.m_ID;


select m.m_ID, u.username, m.message 
from messages m inner join users u on u.u_ID = m.auhor_id
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜