CODEWITHSUNDEEP
mysqlinner-join
stratis
stratis

Reputation: 8042

Inner join in regular mysql tables

In mysql I've got a table called users which contains the list of users in the system...

id | name | surname | active
____________________________

1    John   Doe       True
2    Steve  Smith     True
...

and then there's another table cars which holds the cars each user has bought:

 id | model | brand | cc    | user_id
 ____________________________________

 1    330     BMW     2000    2
 2    Golf    VW      1600    1
 ...

What I need to do is write an sql statement that does the following:

for each of the users who have bought a car, 
show a list of them along with the number of cars they've purchased

For instance: User Steve Smith has a total of 1 car. User John Doe has a total of 1 car. (or whatever the number).

I think I have to do some sort of inner join but I am not entirely sure on how to do that.

Any help would be appreciated.

Upvotes: 0

Views: 36

Answers (1)

Drew
Drew

Reputation: 24949

The inner join will deliver that. For users with no cars, they would not appear. Note the use of column aliases on id to differentiate columns with a join that have same column name in both tables (id).

Note that Sally Higgins does not appear, as inner join (a.k.a. join) fetches matches. A left join would be used to pick up Sally with her lack of car detail in addition to the others.

Schema

create table users
(   id int auto_increment primary key,
    firstName varchar(100) not null,
    lastName varchar(100) not null,
    active int not null
);
insert users(firstName,lastName,active) values ('John','Doe',1),('Steve','Smith',1),('Sally','Higgins',1);

create table cars
(   id int auto_increment primary key,
    model varchar(100) not null,
    brand varchar(100) not null,
    cc int not null,
    user_id int not null -- don't forget a foreign key constraint here
);
insert cars(model,brand,cc,user_id) values ('330','BMW',2000,2),('Golf','VW',1600,1),('Pinto','Ford',1000,1);

Query

select u.id,u.firstName,u.lastName,u.active,c.id as carId,c.model,c.brand,c.cc 
from users u 
join cars c 
on c.user_id=u.id;
+----+-----------+----------+--------+-------+-------+-------+------+
| id | firstName | lastName | active | carId | model | brand | cc   |
+----+-----------+----------+--------+-------+-------+-------+------+
|  1 | John      | Doe      |      1 |     2 | Golf  | VW    | 1600 |
|  1 | John      | Doe      |      1 |     3 | Pinto | Ford  | 1000 |
|  2 | Steve     | Smith    |      1 |     1 | 330   | BMW   | 2000 |
+----+-----------+----------+--------+-------+-------+-------+------+

Left Join to pick up Sally (car-less Sally)

select u.id,u.firstName,u.lastName,u.active,c.id as carId,c.model,c.brand,c.cc 
from users u 
left join cars c 
on c.user_id=u.id;
+----+-----------+----------+--------+-------+-------+-------+------+
| id | firstName | lastName | active | carId | model | brand | cc   |
+----+-----------+----------+--------+-------+-------+-------+------+
|  2 | Steve     | Smith    |      1 |     1 | 330   | BMW   | 2000 |
|  1 | John      | Doe      |      1 |     2 | Golf  | VW    | 1600 |
|  1 | John      | Doe      |      1 |     3 | Pinto | Ford  | 1000 |
|  3 | Sally     | Higgins  |      1 |  NULL | NULL  | NULL  | NULL |
+----+-----------+----------+--------+-------+-------+-------+------+

Upvotes: 1

Related Questions