SQL INNER JOIN with DISTINCT option

Question

0.00/5 (No votes)

See more:

I have 2 tables within an MS Access database:

A VEHICLES table with columns –
Make
Registration_No
Driver
Insurance
Depreciation

A VEHICLE_ALLOCATION table with columns –
Date
Registration_No
Project

The VEHICLES table is a master vehicle listing.
The VEHICLE_ALLOCATION table is a table containing the date that a given vehicle as used on a given project.

I need to produce a report showing how many days a vehicle was used on a given project over a period of a month.
I need to select DISTINCT Registration_No from VEHICLE_ALLOCATION (to get a list of all the vehicles that were used) and with an INNER JOIN all the details about the vehicle from VEHICLES WHERE the VEHICLE_ALLOCATION.DATE is between the 1st and last days of a given month.
There may be, say, 50 vehicles listed in the VEHICLES table but only 10 could have been allocated to projects over a period of a month.
I have been trying for hours to construct a single SQL statement that will return a list of all the VEHICLE details based on a DISTINCT Registration_No obtained from VEHICLE_ALLOCATION.

I can't post any sample code because I have tried so many variations (with zero success) and I wouldn't know which one to post.

Is such a statement constructable? Help anyone?

Thanks in advance.

Posted 17-Jul-14 3:49am

Darrell de Wet

Add a Solution

Comments

nilesh sawardekar 17-Jul-14 10:10am

which are the columns your looking in report?

Darrell de Wet 17-Jul-14 10:14am

Hi Nilesh. All the columns in VEHICLES

2 solutions

Solution 2

You need to properly define join rules. Please, read this article to understand the difference between different types of joins: Visual Representation of SQL Joins[^].

Posted 17-Jul-14 9:54am

Maciej Los

Comments

Darrell de Wet 18-Jul-14 1:53am

A very good article - thanks for the pointer.

Maciej Los 18-Jul-14 16:07pm

You're welcome ;)
Can you accept my answer - formally - to remove the question from unanswered list?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**DiponRoy** · Accepted Answer · 2014-07-17T08:00:00

Well i have tried like this, i used temp tables for sql server, i am not sure would it gonna be same in ms access

SQL

CREATE TABLE #VEHICLES(
	Make VARCHAR(100),
	Registration_No VARCHAR(100),
	Driver VARCHAR(100),
	Depreciation VARCHAR(100)
)
CREATE TABLE #VEHICLE_ALLOCATION(
	Date DATE,
	Registration_No VARCHAR(100),
	Project VARCHAR(100)
)

SELECT *
	FROM #VEHICLES
SELECT *
	FROM #VEHICLE_ALLOCATION

DROP TABLE #VEHICLES
DROP TABLE #VEHICLE_ALLOCATION

SELECT MONTH(GETDATE())
SELECT DAY(GETDATE())


/*Main query*/
SELECT *
	FROM #VEHICLES AS Vehicales
	INNER JOIN 
	(
		SELECT Registration_No, Project, COUNT(*) AS TotalDays
			FROM #VEHICLE_ALLOCATION
			/*Add the conditons*/
			WHERE MONTH(Date) = 1
			AND (DAY(Date) <= 30 AND DAY(Date) >= 1)
			GROUP BY Registration_No, Project
			
	) AS Allocations
	ON Vehicales.Registration_No = Allocations.Registration_No

remove the # signs from the Main query.
if you want to add allocation date also that a different story.