|
willard31 -> 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:: "
|
|
|
|
