Click here to Skip to main content
15,437,442 members
Home / Discussions / Database
   

Database

 
GeneralRe: Multiple columns in LIKE Pin
Andrew Rissing14-Oct-08 6:43
MemberAndrew Rissing14-Oct-08 6:43 
QuestionLoading column having $ sign to SQL Server... Pin
notes4we13-Oct-08 7:56
Membernotes4we13-Oct-08 7:56 
AnswerRe: Loading column having $ sign to SQL Server... Pin
Wendelius13-Oct-08 9:22
mveWendelius13-Oct-08 9:22 
QuestionArchiving a record Pin
Bolivarian13-Oct-08 7:07
MemberBolivarian13-Oct-08 7:07 
AnswerRe: Archiving a record Pin
Wendelius13-Oct-08 9:25
mveWendelius13-Oct-08 9:25 
GeneralRe: Archiving a record Pin
Bolivarian14-Oct-08 0:12
MemberBolivarian14-Oct-08 0:12 
GeneralRe: Archiving a record Pin
Wendelius14-Oct-08 4:54
mveWendelius14-Oct-08 4:54 
QuestionLEFT JOIN - GET ONLY ONE RESULT FROM RIGHT TABLE Pin
polishprogrammer13-Oct-08 7:05
Memberpolishprogrammer13-Oct-08 7:05 
I have a need to execute a query that pulls all data from one table regardless of whether matching data (matched by a foreign key) is present in a second table. This would be a classic LEFT JOIN query except that if a matching record is found in the RIGHT table (table2), then I need to pull only the latest such record from that table.

In particular, I need to pull one field, called VE (maybe more in the future) from the RIGHT table, but only from the latest record in the RIGHT table that matches the data in the LEFT table. The basic scenario I have is an object described in the LEFT table and the results of a process performed on that object in the RIGHT table. There may be more than one process result for that object in the right table, but only the latest one matters to me for the purposes of this query.

To solve this problem I used the following query based on the advice of someone from Code Project, but it didn't quite work out as planned. The ID in this query is an autonumber field that I'm using to retrieve the latest record.

SELECT Details.*, Results.VE
FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);


It worked for the most part, but there were 7 more records in this result table from this query than are present in the 'LEFT' table. It appears that the duplications were occuring in the subquery, such that if there was an object that had two (or more) process results with different VE values, then the duplication occurred. For other duplications, this query correctly retrieved only the last record.

I appreciate any help.

Thanks.
AnswerRe: LEFT JOIN - GET ONLY ONE RESULT FROM RIGHT TABLE Pin
Wendelius13-Oct-08 7:36
mveWendelius13-Oct-08 7:36 
GeneralRe: LEFT JOIN - GET ONLY ONE RESULT FROM RIGHT TABLE Pin
polishprogrammer13-Oct-08 11:01
Memberpolishprogrammer13-Oct-08 11:01 
GeneralRe: LEFT JOIN - GET ONLY ONE RESULT FROM RIGHT TABLE Pin
Wendelius13-Oct-08 18:12
mveWendelius13-Oct-08 18:12 
GeneralRe: LEFT JOIN - GET ONLY ONE RESULT FROM RIGHT TABLE Pin
polishprogrammer14-Oct-08 3:38
Memberpolishprogrammer14-Oct-08 3:38 
GeneralRe: LEFT JOIN - GET ONLY ONE RESULT FROM RIGHT TABLE Pin
Wendelius14-Oct-08 5:02
mveWendelius14-Oct-08 5:02 
GeneralRe: LEFT JOIN - GET ONLY ONE RESULT FROM RIGHT TABLE Pin
polishprogrammer15-Oct-08 7:16
Memberpolishprogrammer15-Oct-08 7:16 
QuestionMS DTC has cancelled the transaction [modified] Pin
moon_stick13-Oct-08 5:10
Membermoon_stick13-Oct-08 5:10 
AnswerRe: MS DTC has cancelled the transaction Pin
Wendelius13-Oct-08 5:50
mveWendelius13-Oct-08 5:50 
GeneralRe: MS DTC has cancelled the transaction [modified] Pin
moon_stick13-Oct-08 6:07
Membermoon_stick13-Oct-08 6:07 
GeneralRe: MS DTC has cancelled the transaction Pin
Wendelius13-Oct-08 7:20
mveWendelius13-Oct-08 7:20 
GeneralRe: MS DTC has cancelled the transaction Pin
moon_stick13-Oct-08 9:24
Membermoon_stick13-Oct-08 9:24 
GeneralRe: MS DTC has cancelled the transaction Pin
Wendelius13-Oct-08 9:35
mveWendelius13-Oct-08 9:35 
GeneralRe: MS DTC has cancelled the transaction Pin
moon_stick15-Oct-08 23:24
Membermoon_stick15-Oct-08 23:24 
GeneralRe: MS DTC has cancelled the transaction Pin
Wendelius16-Oct-08 8:42
mveWendelius16-Oct-08 8:42 
QuestionSql Pin
Rajeesrivastava12-Oct-08 21:25
MemberRajeesrivastava12-Oct-08 21:25 
AnswerRe: Sql - 3RD POST!!!!!!!!!!!!!!!!! Pin
Ashfield12-Oct-08 22:12
MemberAshfield12-Oct-08 22:12 
GeneralRe: Sql - 3RD POST!!!!!!!!!!!!!!!!! Pin
Paul Conrad13-Oct-08 12:51
professionalPaul Conrad13-Oct-08 12:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.