How to use foreign key when querying from two tables

Question

My question is quite basic. It is about how can i build my query with using a foreign key to select certain information from two tables?

table vehicles
+-------+----------+------------+
| id_car| car_model| car_owner  |
+-------+----------+------------+
|  1    |       VW |         132|
+-------+----------+------------+
|  2    |       VW |         200|
+-------+----------+------------+
table users
+-------+----------+------------+
|user_id| user_name| user_phone |
+-------+----------+------------+
|  132  |     Peter|    555-555 |
+-------+----------+------------+
|  200  |      Jim |    555-333 |
+-------+----------+------------+

car_owner is foreign key in vehicles table which references to the primary key user_id of users table.

So someone is searching for all VW cars and i want to have as populate the following information as html(yes, I know that this is not the correct way - i use this just to simplify the example and show which information from each table goes):

>    echo "Car model:". `vehicles.car_model`
>    echo "Car owner:". `users.user_name`
>    echo "Contacts: ". `users.user_phone`

thanks in advance.

Answer 1

I'm not sure, if you understood what foreign keys are used for. A foreign key basically says "for this entry there has to be an entry in the parent table". You said user_id is foreign key in vehicle table, which is not clear for me.

So, let's assume you have a table definition like this:

CREATE TABLE vehicles
(`id_car` int, `car_model` varchar(2), `car_owner` int);


CREATE TABLE users
(`user_id` int, `user_name` varchar(5), `user_phone` varchar(7)
, CONSTRAINT `fk_your_foreign_key` FOREIGN KEY (user_id) REFERENCES vehicles(car_owner)
);

When you want to insert a new user into the table, the user_id must be an existing entry in car_owner column in vehicles table.

Foreign keys are there to implement business rules. Does every user necessarily have to be a car owner? Or the other way round, does every car have to be owned by someone? If you can answer both questions with no, then don't implement any foreign keys for this case. But do so, if you can answer yes for sure.

To get the information you're looking for just do

SELECT 
* 
FROM 
vehicles 
INNER JOIN users ON vehicles.car_owner = users.user_id

Answer 2

You need SQL Join, something like this -

SELECT vehicles.car_model, users.user_name, users.user_phone
FROM vehicles
JOIN users ON vehicles.car_owner = users.users_id