How can I query using a foreign key in MySQL?

Question

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?

http://stackoverflow.com/questions/260441/how-to-create-relationships-in-mysql.... However foreign keys are only supported on InnoDB, you cant do such thing in MyIsam. — Miro Markaravanes, Sep 18 '12 at 18:51
@MiroMarkarian the relationship is setup properly and it is InnoDB, I just didn't understand how to use `JOIN` properly but it was answered below. — Josh Mountain, Sep 18 '12 at 19:02

score 29 · Accepted Answer · answered Sep 18 '12 at 18:51

29

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

answered Sep 18 '12 at 18:51

Clodoaldo Neto

118,695
26
233
260

1

+1 for answering the question and for using ANSI-compliant JOIN syntax. – Aaron Sep 18 '12 at 18:55
1

I tried using this as my query, but when I echo `$row['status_id']` it still outputs `2` – Josh Mountain Sep 18 '12 at 18:55
1

@Riboflavin That is because you are asking for status_id, look at my answer. You need to ask for 'value' if that is indeed the name of the column in the statuses table. – thatidiotguy Sep 18 '12 at 18:56
@thatidiotguy ah of course, that works perfectly thank you! Excuse my noobness, I'm brand new to foreign keys. – Josh Mountain Sep 18 '12 at 18:57

score 6 · Answer 2 · answered Sep 18 '12 at 18:52

6

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'];

answered Sep 18 '12 at 18:52

Iberê

1,211
1
11
16

Tobb · Answer 3 · 2015-04-13T18:56:39.863

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

edited Apr 13 '15 at 18:56

answered Sep 18 '12 at 18:49

Tobb

11,850
6
52
77

score 0 · Answer 4 · answered Sep 18 '12 at 18:50

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.

score 0 · Answer 5 · answered Sep 18 '12 at 18:51

0

You aren't JOINing here:

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

answered Sep 18 '12 at 18:51

CodeTalk

3,571
16
57
92

How can I query using a foreign key in MySQL?

5 Answers5

Linked