eliminating rows from a return set - join problem

[terrell]

Hi:
I am trying to eliminate one of the rows where the linkID is duplicated. The record is different between the two rows with linkID = 1198.

Of course using distinct linkID doesn't work, or any other thing that I have tried. Is there a way to alter my query?

What other techique could I use if not?


QUERY:
***************************************
SELECT
l.linkID,
title,
t.linkTypeID,
t.sortingOrderMain,
t.sortingOrder,
lt.type

FROM links2 l, links_linkTypes2 t, linkTypes2 lt
WHERE l.linkID = t.linkID
AND t.linktypeID = lt.linktypeid
AND bMain =1
AND bApprove = 1
AND (
lt.linkTypeID = 20--@linkTypeID
OR
lt.parentID = 20--@linkTypeID
)
ORDER BY lt.sortingOrder, t.sortingOrderMain


RESULTS:
***************************************

linkID title linkTypeID sortingOrderMain sortingOrder type
----------- ----------------------------------- ---------- ------------------ ----
1048 Eugene City Charter 20 1 2 Eugene
1147 Eugene Land Use Application Info 20 2 4 Eugene
1168 EugeneCC-Agenda 20 3 5 Eugene
1191 Eugene-Spfld Transplan 20 4 8 Eugene
1198 Eugene Land Use Code 20 5 12 Eugene
1198 Eugene Land Use Code 24 6 999 Planning
1028 Eugene Wetlands Program 30 7 999 Wetlands
1008 Eugene Planning Division 24 8 2 Planning

[Steve_LL]

I don't understand what you're asking. Is it how to isolate the row? If so, you have to make sure that the tables are joined properly and select the values that make the row unique...

select *
from links2 l inner join links_linkTypes2 t on l.linkID = t.linkID
inner join linkTypes2 lt on t.linktypeID = lt.linktypeid

where l.linkID = '1198'
and lt.type = 'Eugene'

to delete the row:

delete
from links2 l inner join links_linkTypes2 t on l.linkID = t.linkID
inner join linkTypes2 lt on t.linktypeID = lt.linktypeid
where l.linkID = '1198'
and lt.type = 'Eugene'

Does that help?