SQL notes getting joined twice because they are related to service not unit

Ok I have a database that looks as follows: Orders, Services, OrderUnits, Notes

Orders: id
Services: id, orderid
Units: id, Orderid, unitnumber
Notes: id, relatedid, text, date

• Orders have 1 to many order units
• Orders have 1 to many services
• Services have 1 to many notes

I am trying to relate only the notes that relate to the unit and show those records, the problem is there is no database relation between notes and unit, only ser开发者_StackOverflow社区vice and note.

I am trying to get the following:

select u.unitnumber
      ,n.date
      ,o.id
      ,s.id
      ,n.text
 FROM tblorders o
 INNER JOIN tblServices s on o.id = s.orderid
 INNER JOIN tblUnits u on o.id = u.orderid
 INNER JOIN tblNotes n on s.id = n.RelatedId
 WHERE n.Text LIKE '<p>The status for the Unit # % has changed from % to OUTTOVENDOR</p>'

The problem here is that this gives me the 2 units 4 times if there is 2 units in the same service because the service is related to the note and not the unit.

The note does contain the unit number in it so I was wondering if I could do anything there to somehow relate the note to the unit number?

Thanks!

---

1. If at all possible, please rename your columns to be the same in every table. Don't call it just "ID" in the order table, call it OrderID (capitalization optional). Since RelatedID refers to the service table, change it to ServiceID instead, for crying out loud! This will save so much renaming and aliasing and incorrect joins and general all-around confusion. I promise you this.

2. If Notes are always about Units, they need to be linked to Units relationally through a column. You've simply got to add the column UnitID to the Notes table. Anything else is a horrible, performance-clobbering hack that will come back and haunt you.

3. Even adding UnitID to the Notes table is still not fully normalized because a Note could refer to a Service and a Unit that aren't for the same order. Would you please explain more about what Units and Services are and how they and Notes all relate to each other? I bet there's a way to fix it.

---

If notes should always be related to units, your best best is to fix the design not hack it up with something that will be a performance killer. Why can't you add a column for unitid, populate it once using the hack to get existing data and then have the user interface changed to add the unit id when it adds the note?

Incidentally you are using a text field and those are deprecated, you probably should look at changing it to a varchar(max) as soon as possible (this assumes you are on SQL Server 2005 or higher).

---

Do you need the order units? If you have orders related to services related to notes, the orders -> orderUnits relation will cause your recordset to increase exponentially, like you're seeing, because there isn't a direct relationship there.

---

Assuming that u.unitnumber is an integer:

select u.unitnumber
      ,n.date
      ,o.id
      ,s.id
      ,n.text
 FROM tblorders o
 INNER JOIN tblServices s on o.id = s.orderid
 INNER JOIN tblUnits u on o.id = u.orderid
 INNER JOIN tblNotes n on s.id = n.RelatedId
        AND n.Text LIKE '<p>The status for the Unit # % has changed from % to OUTTOVENDOR</p>' 
        AND CAST(SUBSTRING(n.Text, 30, CHARINDEX(' ', n.Text, 30) - 30) AS int) = u.unitnumber