SQL only want to return a single row for each note

Question

I have the following tables: Orders, Notes, Permits

The following columns are in each table:

Orders = ID
Notes = ID, RelatedID, Note, Timestamp
Permits = ID, OrderId

I have the following query

SELECT o.id
     , op.id
     , n.timestamp
  FROM [tblOrders] o
INNER JOIN [tblNotes] n ON n.[RelatedID] = o.[ID]
INNER JOIN [tblPermits] op ON o.[id] = op.[OrderID]
     WHERE n.[Text] LIKE 'Line item is created%'

An order has 1 to many permits and a order has 1 to many notes

The problem here is that the notes relate to the order and not the individual permit so when you join o.id with n.relatedID if there is more that 1 permit in an order it will actually show 4 records instead of 2 since it joins twice for each permit since the orderID is the same. How can I get this to only return 2 records?

Answer 1

One way would be

SELECT DISTINCT
o.id
,op.id
.....
....

Answer 2

The issue is using JOINs risks duplication in the resultset because there'll be a record for each supporting record in the tblnotes. My first recommendation is to re-write so you aren't using a JOIN:

Using EXISTS:

SELECT o.id,
       p.id
  FROM tblorders o
  JOIN tblpermits p ON p.orderid = o.id
 WHERE EXISTS(SELECT NULL
                FROM tblnotes n 
               WHERE n.[Text] LIKE 'Line item is created%'
                 AND n.relatedid = o.id)

Using IN:

SELECT o.id,
       p.id
  FROM tblorders o
  JOIN tblpermits p ON p.orderid = o.id
 WHERE o.id IN (SELECT n.relatedid
                  FROM tblnotes n 
                WHERE n.[Text] LIKE 'Line item is created%')

Answer 3

SELECT  o.id ,op.id
FROM    [tblOrders] o
JOIN    [tblPermits] op
ON      op.[OrderID] = o.[id]
WHERE   o.id IN
        (
        SELECT  n.[RelatedID]
        FROM    tblNotes n
        WHERE   n.[Text] LIKE 'Line item is created%'
        )