multiple tables select

Question

I don't know if it's possible but this is what I want to do. I have three tables

Accounts

id | username | pass

Cars

id | plate | id_ac | kilometers // in the id_ac I add manual the id of the car's owner.

Trips

id | plate | places | date | active

Now I want when the user is logged in to see tha Trips that are active with his car. So I want to

SELECT from Trips the plate, places, date WHERE active=0 AND id= ? (the logged user's id)

But the table trips doesn't have the id of the owner of the car. So I want somehow to select the values of the table Trips of the car that logged user owns.

Any idea how can I do that?

Answer 1

You should have a look at table joins. This looks like it is what you are looking for:

SELECT t.plate, t.places, t.date FROM Trips as t JOIN Cars as c ON t.plate = c.plate WHERE t.active = 0 AND c.id_ac = ?

Have a look at this. This is a pretty nice way of explaining the table joins. http://www.sql-join.com/sql-join-types

I am not getting into DB architecture and deeper stuff, but it can help you with your task at hand