How to use a foreign key when querying from two tables

Question

My question is pretty simple. This is about how I can build my query using a foreign key to select specific 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 | +-------+----------+------------+ 

user_id is the foreign key in the vehicle.car_owner table and the primary key for the users table.

So, someone is looking for all VW cars, and I want the following information to be filled in as html (yes, I know that this is not the right way), I use this only to simplify the example and show what information from each table goes):

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

early.

Answer 1

I am not sure if you understand what foreign keys are used for. The foreign key basically says: "there must be an entry in the parent table for this record." You said user_id is foreign key in vehicle table , which is not clear to me.

So let's say you have a table definition as follows:

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

If you want to insert a new user into the table, user_id must be existing in the car_owner column in the vehicle table.

There are foreign keys for implementing business rules. Every user must be the owner of the car? Or vice versa, should every car belong to someone? If you can answer both questions without a no, then do not use foreign keys for this case. But do it if you answer “Yes” for sure.

To get the information you are looking for, simply follow

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

Answer 2

Use JOIN:

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

To deploy: vehicles INNER JOIN users will only return vehicles that have owners. If you want to display cars that have NULL for the owner or remote owner, use vehicles LEFT JOIN users .

For vehicles INNER JOIN users , another way is also possible:

 SELECT * FROM vehicles, users WHERE vehicles.car_owner=users.user_id 

The latter selects rows matching the condition from the Cartesian product of two tables.

Answer 3

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