|
| |
|
|
willard31
Posts: 8
Joined: 11/10/2004
Status: offline
 |
SQL Left Join eliminating duplicates - 1/7/2005 23:43:21
Maybe someone can help with the correct syntax for what I'm trying to accomplish. I believe the LEFT JOIN is the way to go, but I'm missing something.
I have a table from SupportLog that displays a list of calls to our helpdesk, sortable by LogID, LogLocation, LogDate, etc. A separate table, SupportDetail, records notes that are added to each call. LogID and SDLink link the two tables.
Some records have many notes, some have none. I would like to display a "*" next to the LogID of those that have notes, so the supporter knows to click the link and read the notes.
The following statement returns all calls and I'm able to put the "*" next to those that have notes, but the record is repeated for however many notes exist. So if LogID 29384 has 3 notes in the SupportDetail table, it is repeated 3 times in the recordset. I've tried playing with DISTINCT, RIGHT JOIN, etc to no avail. Database resides on SQL Server.
strSQL = "SELECT * FROM SupportLog LEFT JOIN SupportDetail ON (SupportLog.LogID=SupportDetail.SDLink) WHERE (LogAssigned LIKE '::LogAssigned::' AND LogComplete = ::LogComplete:: AND LogContact LIKE '::LogContact::' AND (LogShort LIKE '%::Search::%' OR LogLong LIKE '%::Search::%')) ORDER BY ::Sort:: ::Order:: "
|
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: SQL Left Join eliminating duplicates - 1/8/2005 13:23:59
Without a lot of thought on this, have you tired adding a GROUP BY clause?
strSQL = "SELECT * FROM SupportLog LEFT JOIN SupportDetail ON (SupportLog.LogID=SupportDetail.SDLink) WHERE (LogAssigned LIKE '::LogAssigned::' AND LogComplete = ::LogComplete:: AND LogContact LIKE '::LogContact::' AND (LogShort LIKE '%::Search::%' OR LogLong LIKE '%::Search::%')) GROUP BY LogID ORDER BY ::Sort:: ::Order:: "
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
willard31
Posts: 8
Joined: 11/10/2004
Status: offline
|
RE: SQL Left Join eliminating duplicates - 1/8/2005 16:10:42
If the statement was SELECT LogID rather than SELECT *, I think this would work. Adding GROUP BY LogID returns the following error (LogDate is the next field in the table after LogID)...
Database Results Error
Description: Column 'SupportLog.LogDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for SQL Server
| |
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: SQL Left Join eliminating duplicates - 1/8/2005 22:33:00
Perhaps this will work for you?
http://www.outfront.net/spooky/adv_grouping.htm
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 