Left Join Not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

For some reason I can not get this LEFT JOIN SQL to work. According to the
SQL below I should only see one Ticket# from the TroubleTicket Table, but
when I run this query it shows a Ticket# for every Action#. Can someone tell
me what I am doing wrong? I have done this several times before and it
worked just fine.

SELECT TroubleTicket.[Ticket#] AS [TroubleTicket_Ticket#],
TroubleTicket.RequestDate, TroubleTicket.[RequestDate&Time],
TroubleTicket.RequestedBy, TroubleTicket.Type,
TroubleTicket.DescriptionOfProblem, TroubleTicket.Priority,
TroubleTicket.Status, TroubleTicket.Notes, TroubleTicketActionList.[Action#],
TroubleTicketActionList.[Ticket#] AS [TroubleTicketActionList_Ticket#],
TroubleTicketActionList.Date, TroubleTicketActionList.CompletedBy,
TroubleTicketActionList.ActionTaken
FROM TroubleTicket LEFT JOIN TroubleTicketActionList ON
TroubleTicket.[Ticket#] = TroubleTicketActionList.[Ticket#];
 
Looks to me like the query is working properly. I think that you might
misunderstand what the Left join does. With an Inner join you wouldn't see
any records for a particular Ticket# unless there was a matching record in
each table.

With the Left join, the TroubleTicket record will show up even without a
matching record in TroubleTicketActionList.

If you need just one TroubleTicket record and just one matching
TroubleTicketActionList record, you'll need to write a fancy subquery.
 
For some reason I can not get this LEFT JOIN SQL to work. According to the
SQL below I should only see one Ticket# from the TroubleTicket Table, but
when I run this query it shows a Ticket# for every Action#. Can someone tell
me what I am doing wrong? I have done this several times before and it
worked just fine.

SELECT TroubleTicket.[Ticket#] AS [TroubleTicket_Ticket#],
TroubleTicket.RequestDate, TroubleTicket.[RequestDate&Time],
TroubleTicket.RequestedBy, TroubleTicket.Type,
TroubleTicket.DescriptionOfProblem, TroubleTicket.Priority,
TroubleTicket.Status, TroubleTicket.Notes, TroubleTicketActionList.[Action#],
TroubleTicketActionList.[Ticket#] AS [TroubleTicketActionList_Ticket#],
TroubleTicketActionList.Date, TroubleTicketActionList.CompletedBy,
TroubleTicketActionList.ActionTaken
FROM TroubleTicket LEFT JOIN TroubleTicketActionList ON
TroubleTicket.[Ticket#] = TroubleTicketActionList.[Ticket#];
Click to expand...

Why do you feel that you should see only one Ticket#? Is Ticket# the Primary
Key of either table? If it's the primary key of TroubleTicket, then you should
see as many records for each Ticket# as there are records in
TroubleTicketActionList for that Ticket# (and one record with NULL values for
that field if there are no matching records).

If there are multiple Action records for each Ticket, and you expect to see
only one of them - which one do you expect to see, and why?

John W. Vinson [MVP]
 
Yes, the Ticket# is the PK of the TroubleTicket Table and is the One part of
the One to Many Relationship. The many part of the relationship is on the
Ticket# field of the TroubleTicketActionList Table. The only
TroubleTicketActionList.Ticket# I want to see is the last one entered(which
would have the largest TroubleTicketActionList.ActionID). Each Action has a
time, and I am looking to take the time in the Ticket and the time in the
action to get an average response time. I dont understand why this wont
work, it worked with this SQL.

SELECT BatchTracking.Database, BatchTracking.[CPUBatch#], ([Database]) & ""
& ([CPUBatch#]) AS Name, BatchTracking.BatchType, BatchTracking.Date,
BatchTracking.DepositDate, BatchTracking.PostedToDate, IIf(([PostedBy]) Is
Not Null,"Closed","Open") AS Status, BatchTracking.BatchID,
BatchTracking.TotalDeposit, BatchTracking.CMBSBatchTotal,
Discrepancies.Amount, BatchTracking.TotalPostedInCPU,
NZ([TotalDeposit])-NZ(Discrepancies.Amount)-NZ([TotalPostedInCPU]) AS
Variance, Discrepancies.DiscrepancyID, BatchTracking.Void
FROM BatchTracking LEFT JOIN Discrepancies ON BatchTracking.BatchID =
Discrepancies.BatchID
WHERE (((BatchTracking.Void)=No));

This SQL shows me only one instance of BatchTracking according to the
BatchID. There can be multiple Discrepancies per BatchID, but when I made
this a LEFT JOIN it only showed one instance of each BatchID.

John W. Vinson said:
For some reason I can not get this LEFT JOIN SQL to work. According to the
SQL below I should only see one Ticket# from the TroubleTicket Table, but
when I run this query it shows a Ticket# for every Action#. Can someone tell
me what I am doing wrong? I have done this several times before and it
worked just fine.

SELECT TroubleTicket.[Ticket#] AS [TroubleTicket_Ticket#],
TroubleTicket.RequestDate, TroubleTicket.[RequestDate&Time],
TroubleTicket.RequestedBy, TroubleTicket.Type,
TroubleTicket.DescriptionOfProblem, TroubleTicket.Priority,
TroubleTicket.Status, TroubleTicket.Notes, TroubleTicketActionList.[Action#],
TroubleTicketActionList.[Ticket#] AS [TroubleTicketActionList_Ticket#],
TroubleTicketActionList.Date, TroubleTicketActionList.CompletedBy,
TroubleTicketActionList.ActionTaken
FROM TroubleTicket LEFT JOIN TroubleTicketActionList ON
TroubleTicket.[Ticket#] = TroubleTicketActionList.[Ticket#];
Click to expand...

Why do you feel that you should see only one Ticket#? Is Ticket# the Primary
Key of either table? If it's the primary key of TroubleTicket, then you should
see as many records for each Ticket# as there are records in
TroubleTicketActionList for that Ticket# (and one record with NULL values for
that field if there are no matching records).

If there are multiple Action records for each Ticket, and you expect to see
only one of them - which one do you expect to see, and why?

John W. Vinson [MVP]
Click to expand...
 
Yes, the Ticket# is the PK of the TroubleTicket Table and is the One part of
the One to Many Relationship. The many part of the relationship is on the
Ticket# field of the TroubleTicketActionList Table. The only
TroubleTicketActionList.Ticket# I want to see is the last one entered(which
would have the largest TroubleTicketActionList.ActionID). Each Action has a
time, and I am looking to take the time in the Ticket and the time in the
action to get an average response time. I dont understand why this wont
work, it worked with this SQL.

SELECT BatchTracking.Database, BatchTracking.[CPUBatch#], ([Database]) & ""
& ([CPUBatch#]) AS Name, BatchTracking.BatchType, BatchTracking.Date,
BatchTracking.DepositDate, BatchTracking.PostedToDate, IIf(([PostedBy]) Is
Not Null,"Closed","Open") AS Status, BatchTracking.BatchID,
BatchTracking.TotalDeposit, BatchTracking.CMBSBatchTotal,
Discrepancies.Amount, BatchTracking.TotalPostedInCPU,
NZ([TotalDeposit])-NZ(Discrepancies.Amount)-NZ([TotalPostedInCPU]) AS
Variance, Discrepancies.DiscrepancyID, BatchTracking.Void
FROM BatchTracking LEFT JOIN Discrepancies ON BatchTracking.BatchID =
Discrepancies.BatchID
WHERE (((BatchTracking.Void)=No));

This SQL shows me only one instance of BatchTracking according to the
BatchID. There can be multiple Discrepancies per BatchID, but when I made
this a LEFT JOIN it only showed one instance of each BatchID.
Click to expand...

There's something different then about these two queries.

A Left Join does what Jerry, and I, and the Help file say that it does: it
returns ALL of the records in the child table which match each record in the
parent table. If you're assuming that it returns only the latest record, your
assumption is simply incorrect.

To get just the most recent record, use an Inner Join (because a Left Join
will return records where there is a TroubleTicket and *no action at all*, not
to speak of a most recent action); and put a criterion on the ActionID of

=(SELECT Max(X.[ActionID]) FROM TroubleTicketActionList AS X WHERE X.[Ticket#]
= [TroubleTickets].[Ticket#])

This "subquery" will select only the largest value of ActionID. It might be
safer to use the maximum value of ActionDate as a criterion on ActionDate, but
if you trust your ID's to be strictly increasing (which you CANNOT safely do
for autonumbers!) you're OK.

John W. Vinson [MVP]
 
Back
Top