CODEWITHSUNDEEP
phpsql
NebulaBoy
NebulaBoy

Reputation: 31

multiple tables select

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?

Upvotes: 3

Views: 67

Answers (2)

davidev
davidev

Reputation: 8517

What you are referring to is primary and forgein key.

Your primary key is a unique key, which can identify every record in your table. In your user table that is the id. It has to be unique.

When you are now creating trips in your trips table, you will have to "link" your record to the specific user. Here you are referring to the primary key in user's table. So you have a new column user_id inside your trips table, where you will store the user id, who is connected to that trip.

When using the user id in other tables, you are having a "foreign key".

For each trip you will have to create a foreign key for the car and for the user. This can be the number plate and the user id.

If you want to see all the tracks for a specific user, you can make a JOIN with the user id in tracks. If you want to see all tracks for one car, you will just JOIN with the plate.

(I can just suggest reading some information about primary/ foreign key. It is fundamental when designing a database structure as you did above.)

Upvotes: 0

Cornel Raiu
Cornel Raiu

Reputation: 2995

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

Upvotes: 1

Related Questions