SQLTeam.com | Weblogs | Forums

SQL combine 3 tables

Hi All

I'm new to the forum but hoping you guys and girls can help me out.
Form a couple of days now I'm strugling to get an sql querry working.

In a fleet management app I'm trying to display all the fines/speeding tickets a drivers has on his car.
The problem is here that the cars are swapped between different drivers so I end up with 3 tables.
One that holds my dirvers, one that holds the fines, and one that tracks who drove what when.

--------------------------------------------------
CREATE TABLE #drivers (
    id int NOT NULL,
    drivercode varchar(30) NOT NULL,
    name varchar(50) NOT NULL
    )
INSERT INTO #drivers VALUES
    (1, 'snowjo', 'John Snow'),
    (2, 'yeti', 'Sasquatch')
--------------------------------------------------
CREATE TABLE #carusage (
    id int NOT NULL,
    drivercode varchar(30) NOT NULL,
    plate varchar(30) NOT NULL,
    fromdate date NOT NULL,
    todate date NOT NULL,
    )
INSERT INTO #carusage VALUES
    (1, 'snowjo', 'XXV123', '20190225', '20190228'),
    (2, 'yeti', 'HAIRYBEAST', '20190224', '20190228')
--------------------------------------------------
--DROP TABLE #fines
CREATE TABLE #fines (
    id int NOT NULL,
    plate varchar(30) NOT NULL,
    date date NOT NULL,
    fineamount decimal(9, 2) NULL,
    location varchar(30) NULL
    )
INSERT INTO #fines VALUES
    (1, 'XXV123', '20190226', 25.00, 'Texas'),
    (2, 'HAIRYBEAST', '20190226', 75.00, 'Himalayas')
--------------------------------------------------

So if I want to display the fines of the driver 'snowjo' I only want to see one record, the one infraction on 2019-02-26 as he was driving the car between 2019-02-25 and 2019-02-30

I'm thinking about using an INNER JOIN or somehting?
Help would be appreciated.

Welcome to this forum!

It's extremely helpful for people who answer qs if you provide data in a usable fashion, like this:

--------------------------------------------------
CREATE TABLE #drivers (
    id int NOT NULL,
    drivercode varchar(30) NOT NULL,
    name varchar(50) NOT NULL
    )
INSERT INTO #drivers VALUES
    (1, 'snowjo', 'John Snow'),
    (2, 'yeti', 'Sasquatch')
--------------------------------------------------
CREATE TABLE #carusage (
    id int NOT NULL,
    drivercode varchar(30) NOT NULL,
    plate varchar(30) NOT NULL,
    fromdate date NOT NULL,
    todate date NOT NULL,
    )
INSERT INTO #carusage VALUES
    (1, 'snowjo', 'XXV123', '20190225', '20190228'),
    (2, 'yeti', 'HAIRYBEAST', '20190224', '20190228')
--------------------------------------------------
--DROP TABLE #fines
CREATE TABLE #fines (
    id int NOT NULL,
    plate varchar(30) NOT NULL,
    date date NOT NULL,
    fineamount decimal(9, 2) NULL,
    location varchar(30) NULL
    )
INSERT INTO #fines VALUES
    (1, 'XXV123', '20190226', 25.00, 'Texas'),
    (2, 'HAIRYBEAST', '20190226', 75.00, 'Himalayas')
--------------------------------------------------

Using that data, here is one query that should do what you want. If this doesn't meet your actual requirements, we can adjust the query as needed:

DECLARE @drivercode varchar(30)
SET @drivercode = 'snowjo'

SELECT cu.id, cu.drivercode, cu.plate, f.date, f.fineamount, f.location
FROM #carusage cu
INNER JOIN #fines f ON f.plate = cu.plate AND
    f.date BETWEEN cu.fromdate AND cu.todate
WHERE cu.drivercode = @drivercode
1 Like

Thank you for the quick reply, I works :+1:
I'll keep the posting rules in mind next time.