Alordiel Alordiel - 1 year ago 38
SQL Question

How to use foreign key when querying from two tables

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 |

is foreign key in
table and primary key for

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.


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:

(`id_car` int, `car_model` varchar(2), `car_owner` int);

(`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

INNER JOIN users ON vehicles.car_owner = users.user_id