CODEWITHSUNDEEP
phpmysqlsql
Ale
Ale

Reputation: 1032

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 |
+-------+----------+------------+

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.

Upvotes: 16

Views: 67016

Answers (5)

mr. MTS
mr. MTS

Reputation: 1

select *
from
vehicles
inner join users on vehicles.car_owner = users.user_id 
WHERE id_car = 2

This query is showing only one row, which is id 2.

Upvotes: 0

gristy
gristy

Reputation: 575

from a learner's perspective, it is striking that the foreign keys don't appear to be functional wrt linking tables in queries: i.e. the queries given in the 3 answers above work w/o foreign keys. e.g. for the last example, a mysql learner would expect mysql to implicitly infer from a foreign key that vehicle.car_owner = user.users_id so that when querying

SELECT vehicles.car_model, users.user_name, users.user_phone
FROM vehicles JOIN users

should suffice. While the query code :

 ON vehicles.car_owner = users.users_id

looks like redundant explicit re-statement of the foreign key

Upvotes: 4

Adder
Adder

Reputation: 5868

Use JOIN:

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

To expand: vehicles INNER JOIN users will return only cars that have owners. If you want to display cars that have a NULL for owner, or a deleted owner, then use vehicles LEFT JOIN users.

For vehicles INNER JOIN users there is also a different way possible:

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

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

Upvotes: 2

fancyPants
fancyPants

Reputation: 51888

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

Upvotes: 16

Rishabh
Rishabh

Reputation: 2021

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

Upvotes: 2

Related Questions