CODEWITHSUNDEEP
sqlselect
Ellbar
Ellbar

Reputation: 4054

Selecting data from two joined tables

I have 2 tables: Users and Cars. The tables are joined in third table: Users_Cars_Join.

Is there any fast (direct) method to select data from both tables (Cars, Users) without using 'inner join' statement to join Users with Users_Cars_Join and than using this one more time to join Users_Cars_join to Cars?

I wonder how it should be done correctly and profesionally.

Cars:

Users:

Users_Cars_Join:

Upvotes: 1

Views: 1709

Answers (2)

Rachcha
Rachcha

Reputation: 8816

You can use the old stye for joining tables like this:

SELECT c.id, c.name, u.id, u.name, ...
  FROM cars c, users u, user_cars_join uc
 WHERE u.id = uc.userid
   AND c.id = uc.carid

Things to note:

  1. The user_cars_join table exists because it denotes the many-to-many relationship between users and their cars. If you remove this table from the structure then the users and cars tables are not related anymore.

  2. You need to understand the phenomena of one-to-one, one-to-many and many-to-many relationships.

    • A one-to-one relationship is where one entity in one set relates to only one entity in the other set. In your case it would have been one user owning only one single car which is not practical. A single person can own multiple cars.

    • A one-to-many relationship is where one entity in one set relates to multiple entities in the other set, and, one entity in the other set relates to only one entity in the first set. In your case it would have been one user owns multiple cars but a single car is owned by a single user. You can have this sort of relationship, especially when the car has a registration number. The schema would look something like this:

      User (userid, name)
car_sales (carid, userid, registration_number)
Car (carid, name, brand, model)

      Note that the userid in the car_sales table denotes which user owns this car. Also, the carid in car_sales table refers to the unique carid in car table.

      Also note that in this model, one user can own multiple cars. One model of car can have multiple instances (items of it) sold to multiple users.

      This is the most widely used and acceptable model for representing these relationships.

    • A many-to-many relationship, as implemented in your example, is when multiple entities in one set are related to multiple entities in the other set and vice versa. There has to be a separate relationships table to denote the relationships between the entities, which in your case is the user_car_join table.

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58451

There's no way to bypass the linktable but if you dread writing the joins all the time, you can create a view that does just that

CREATE VIEW UsersCars AS 
SELECT  c.ID AS CarID
        , c.Name AS CarName
        , u.ID AS UserID
        , u.Name AS UserName
FROM    Cars c
        INNER JOIN Users_Cars_Join ucj ON ucj.CarID = c.ID
        INNER JOIN Users u ON u.ID = ucj.UserID

Using the view would be as simple as 

SELECT * FROM UsersCars

See this SQL Fiddle for a demo

Upvotes: 2

Related Questions