How can I query using a foreign key in MySQL?

问题:

Right now I have a small database with two tables that look something like this:

    users table
    ====================
    id  name   status_id
    1   Bobby  3
    2   James  2

and

    statuses table
    =============
    id  value
    1   Waiting
    2   Approved
    3   Other

status_id is setup as a foreign key constraint to id from the statuses table. My query looks something like this:

SELECT *
FROM `users`
WHERE `status_id` = 2";

When I display $row['status_id'] it outputs 2 but I would like it to display as Approved instead, what is the best way to accomplish this?

回答1:

SELECT u.*, s.*
FROM users u
    inner join statuses s on u.status_id = s.id
WHERE u.status_id = 2

回答2:

What you need is this

SELECT *
FROM `users`
JOIN statuses ON statuses.id = users.status_id
WHERE `status_id` = 2";

and then you can refer to

$row['value'];

回答3:

The easiest way would be through joins:

select *
from User u join Status s on u.status_id = s.id;

(if you dont want the status-id at all, you can specify the columns that you do want in the select-clause.)

回答4:

Your users table does not have the value of approved in it. It is in your statuses table. When you request status_id you are going to get that value back from that query. You have to do a JOIN ON status_id to make this work out I think. Or do a second query.

回答5:

You aren't JOINing here:

SELECT *
FROM Users U, Statuses S
WHERE S.id=U.status_ID
AND status_id = 2;