473,854 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Related Tables: Help Needed With JOIN Query

Hi Group,

My apologies for the lengthy post, but here goes...

I have the following tables:

TABLE Vehicles
(
[ID] nvarchar(5),
[Make] nvarchar(20),
[Model] nvarchar(20),
)

TABLE [Vehicle Status]
(
[ID] int, /* this is an auto-incrementing field*/
[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */
[Status] nvarchar(20),
[Status Date] datetime
)

Here's my problem...

I have the following data in my [Vehicles] and [Vehicle Status] tables:

[ID] [Make] [Model]
----------------------
H80 Nissan Skyline
H86 Toyota Aristo
[ID] [Vehicle ID] [Status] [Status Date]
----------------------------------------
1 H80 OK 2006-10-01
2 H80 Damage 2006-10-05
3 H86 OK 2006-10-13
4 H86 Dent 2006-10-15
5 H86 Scratched 2006-10-16

I need a query that will join the two tables so that the most recent
status of each vehicle can be determined. I've gotten as far as:

SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM
[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [Vehicle
Status].[Vehicle ID]

Of course this produces the following results:

[ID] [Make] [Model] [Status] [Status Date]
--------------------------------------------
H80 Nissan Skyline OK 2006-10-01
H80 Nissan Skyline Damage 2006-10-05
H86 Toyota Aristo OK 2006-10-13
H86 Toyota Aristo Dent 2006-10-15
H86 Toyota Aristo Scratched 2006-10-16

How do I filter these results so that I get only the MOST RECENT vehicle
status?

i.e:

[ID] [Make] [Model] [Status] [Status Date]
--------------------------------------------
H80 Nissan Skyline Damage 2006-10-05
H86 Toyota Aristo Scratched 2006-10-16

Thanks in advance,
Rommel the iCeMAn

*** Sent via Developersdex http://www.developersdex.com ***
Oct 16 '06 #1
1 1339
SELECT v.[ID], Make, Model, [Status], [Status Date]
FROM
[Vehicles] v INNER JOIN [Vehicle Status] vs ON v.[ID] = vs.[Vehicle ID]
and vs.[Status Date] = (select max(vs2.[Status Date]) from [Vehicle
Status] vs2 where vs2.[Vehicle ID] = vs.[Vehicle ID])

www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Oct 16 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1817
by: Cpt. Zeep | last post by:
Although this is not strictly PHP related question, i presume lots of you are good in MySql so maybe you could help me. I am making sort of yellowpages application and have following issue: I have different tables for storing info about different companies, their addresses & phone numbers and their field of work (i can't remember exact word in english ;) Tables look like this: 1. Table "company": ID, name, web, email.... 2. Table...
8
3381
by: Rigga | last post by:
Hi, I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date order. So I need to write a query that retrieves all the information for one record, lets say I want to query on the main table any entry that is for the 2004-01-06 and this date is also in a field called 'Date' in the other tables, how do I go...
3
11118
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I have a primary form named TestResults, which is connected to data in a table named TestResults. There are basically two other tables that are related to the TestResults table (and the primary form) named Names-Normalized and SiteAddresses. The...
4
1152
by: bhushanvinay | last post by:
i have a single souce table , Table a With contiains records for two different entries for the same vendor by different accounting instructions, BidId = 10,Person Name=ABC,PersonBidAmt=$100 BidId = 11,Person Name=ABC,PersonBidAmt=$110 now the recors are to be shown as Original Rcds Vs IntutiveRecords
1
2058
by: luxmanpai | last post by:
Hello All, I have an performance issue, where a query which is executed at the client place which is retrieving 370 records is taking around 10 minutes, Where as the same query which is executed here in our development servers is retrieving 1300 odd records in less than 4 minutes. I checked the client place for Indexes and rebuilt all the indexes as the tables that were used were major tables. I also had a look into the tablespaces which...
10
7714
by: Jim Devenish | last post by:
I have a split front end/back end system. However I create a number of local tables to carry out certain operations. There is a tendency for the front end to bloat so I have set 'compact on close' I think that I have read in some threads (althoug I cannot find them now) that others place such tables in a local, linked database. I could do this but I am interested to know what would be the advantages. And disadvantages, if any. Any...
5
4108
by: rdemyan via AccessMonster.com | last post by:
I have a need to add another field to all of my tables (over 150). Not data, but an actual field. Can I code this somehow. So the code presumabley would loop through all the tables, open each table in design mode and then add the new field and set its properties. Thanks. --
6
1972
by: John | last post by:
Hi I have three tables with a common id with which they can be linked. I need to merge them in a way that the resultant table has all records from three tables. Below is what sort of result I am expecting; Table 1 ID Value1 1 A1
1
1737
by: empiresolutions | last post by:
Hello Fellow Programmers, I have an issue with a very large PHP page that calls over 20 different MySql Statements. This page is loading somewhat slow and i want to speed things up. I have looked into sql caching applications like http://eaccelerator.net/ and http://www.danga.com/memcached/ which i may install later, but i feel they are more for pages that are called frequently, my page calls the same data less times but can call more than...
0
9899
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9750
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10672
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7909
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7076
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5738
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5937
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4550
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3182
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.