Problem with Select from 2 tables :(

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with Select from 2 tables :(

Author

Topic

BenHur
Starting Member

2 Posts

Posted - 2008-09-02 : 06:43:43

Hello:
i have to tables: CARS (Car_id, Car_name, User_id) and ODOMETER_COUNTER(Odometer_Counter.id, Car_id, Odometer, Date)

I want to take the car_id from the CARS table (because later i want to use User from that car_id...) and the Odometers LAST Read.

The problem is that with inner join, if i have multiple READINGS of CAR for ex with this select:

quote:
SELECT DISTINCT Cars.Car_id AS Expr1, Odometer_Counter.Date
FROM Cars INNER JOIN
Odometer_Counter ON Cars.Car_id = Odometer_Counter.Car_id
GROUP BY Cars.Car_id, Odometer_Counter.Date

it prints out:
4 8/25/2008 12:00:00 AM
5 8/1/2008 9:27:47 AM
6 8/1/2008 9:27:47 AM
6 8/26/2008 12:00:00 AM

so Car_id:6 appears twice, i want only last!.

Please help me.

BenHur
Starting Member

2 Posts

Posted - 2008-09-02 : 06:49:54

Finally sovled:

quote:
SELECT DISTINCT Cars.Car_id AS Expr1, Odometer_Counter.Counter, Odometer_Counter.Date
FROM Cars INNER JOIN
Odometer_Counter ON Cars.Car_id = Odometer_Counter.Car_id
WHERE (Odometer_Counter.Date =
(SELECT TOP 1 Date
FROM Odometer_Counter AS b
WHERE (Cars.Car_id = Car_id)))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-02 : 08:46:54

FYI, you dont need to use DISTINCT when you use GROUP BY Clause

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-09-02 : 09:10:03

Unfortunately Charlton has neither used GROUP BY nor done much testing.

SELECT C.car_id, O.Counter, O.[Date]
FROM Cars C
    JOIN Odometer_Counter O
        ON C.car_id = O.car_id
    JOIN
    (
        SELECT O1.car_id, MAX(O1.[Date]) AS [Date]
        FROM Odometer_Counter O1
        GROUP BY O1.car_id
    ) D
        ON O.car_id = D.car_id
            AND O.[Date] = D.[Date]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-02 : 12:46:41

quote:
Originally posted by BenHur

Finally sovled:

quote:
SELECT DISTINCT Cars.Car_id AS Expr1, Odometer_Counter.Counter, Odometer_Counter.Date
FROM Cars INNER JOIN
Odometer_Counter ON Cars.Car_id = Odometer_Counter.Car_id
WHERE (Odometer_Counter.Date =
(SELECT TOP 1 Date
FROM Odometer_Counter AS b
WHERE (Cars.Car_id = Car_id)))

I'd use Ifor's query as it probably will perform better. (removing the last AND portion with the date comparison) :)

Back to your query.. If there are mutiple odometer rows for a given car you will get a random row, not the most recent. So, your WHERE clause is not quite correct. Here are a couple of examples that expand on the correlated sub-query you used:

SELECT 
	Cars.Car_id AS Expr1, 
	Odometer_Counter.Counter, 
	Odometer_Counter.Date
FROM 
	Cars 
INNER JOIN
	Odometer_Counter 
	ON Cars.Car_id = Odometer_Counter.Car_id
WHERE 
	Odometer_Counter.Date =
		(
			SELECT TOP 1 b.Date
			FROM Odometer_Counter AS b
			WHERE (Cars.Car_id = Car_id)
			ORDER BY Date DESC
		)

-- OR

SELECT 
	Cars.Car_id AS Expr1, 
	Odometer_Counter.Counter, 
	Odometer_Counter.Date
FROM 
	Cars 
INNER JOIN
	Odometer_Counter 
	ON Cars.Car_id = Odometer_Counter.Car_id
WHERE 
	Odometer_Counter.Date =
		(
			SELECT MAX(b.Date)
			FROM Odometer_Counter AS b
			WHERE (Cars.Car_id = Car_id)
		)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources