Get the owner and their mileage with SQL

Question

So I have two tables using MySql; Owners and Cars, where an owner has a birthdate and the cars have mileage records. The tables look kinda like this:

Owners:
+-------+--------------+
| Field | Type         |
+-------+--------------+
| id    | varchar(10)  |
| birth | datetime     |
| name  | varchar(100) |
+-------+--------------+

Cars:
+--------------+-------------+
| Field        | Type        |
+--------------+-------------+
| plate        | varchar(6)  |
| mileage      | int(11)     |
| owner*       | varchar(10) | //fk references Owners
+--------------+-------------+

I want to get to get the youngest owner and their mileage selected using SQL. I've managed to get the car of the owner being the youngest, but I don't know how I could like JOIN the tables and create a table containg the Owner-attributes AND the mileage-attribute from their car.

SELECT * 
FROM Cars 
WHERE owner = (SELECT id 
               FROM Owners 
               WHERE birth = (SELECT MAX(birth) FROM Owners));

Which I said returns the car e.g.

+--------+---------+------------+
| plate  | mileage | owner      |
+--------+---------+------------+
| KFO405 |    8109 | 9912218527 |
+--------+---------+------------+

But I want it like this:

+------------+---------------------+-----------------------+------------+
| id         | birth               | name                  | milage     |
+------------+---------------------+-----------------------+------------+
| 9912218527 | 1999-12-21 00:00:00 | Marva Rocha           |       8109 |
+------------+---------------------+-----------------------+------------+

Any help is appreciated!

The one who downvoted, I'm not usually bothered, but I just can't see how this question bothered anyone — Jesper, Jan 12 '18 at 13:10
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Kurt Van den Branden, Jan 12 '18 at 13:13
Why is that some kind of Communist country people can only have a car? :) Anyway if the relationship is 1:1 then why you have a separated table for `Cars`? just add it as additional fields for `Owners.` — Juan Carlos Oropeza, Jan 12 '18 at 13:14
@JuanCarlosOropeza Not a communist country, just a small example-solution hehe. Owners won't be in BCNF if I do — Jesper, Jan 12 '18 at 13:16
@Strawberry Yes, every car can have a single owner, Unless again you are in a Communist / Socialist Country where the properties is for everyone but the reality is just the one in the power get all the benefits. — Juan Carlos Oropeza, Jan 12 '18 at 14:40

Bibin Mathew · Accepted Answer · 2018-01-12T13:08:44.087

2

To get the youngest owner and mileage

  SELECT A.ID, A.BIRTH, A.NAME, B.MILAGE
  FROM Owners A
      INNER JOIN Cars B
      ON A.ID=B.owner
  ORDER BY A.birth ASC LIMIT 1;

edited Jan 12 '18 at 13:08

answered Jan 12 '18 at 13:06

Bibin Mathew

455
3
11

Jan Zeiseweis · Answer 2 · 2018-01-12T13:14:25.163

1

You basically join the tables first and then define the criteria (Birth):

SELECT Owners.name,
       Owners.birth,
       Cars.plate,
       Cars.milage
FROM Owners
JOIN Cars ON Cars.owner = Owners.id
WHERE Owners.birth =
        (SELECT MAX(birth)
         FROM Owners)

edited Jan 12 '18 at 13:14

answered Jan 12 '18 at 13:08

Jan Zeiseweis

3,718
2
17
24

Misspelled "Owners" to "Ownders", otherwise a useful solution as well – Jesper Jan 12 '18 at 13:14
Thanks. I fixed the spelling. – Jan Zeiseweis Jan 12 '18 at 13:14

Get the owner and their mileage with SQL

2 Answers2