Notes Table is someone's bad idea of a joke

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Notes Table is someone's bad idea of a joke

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-07-02 : 17:12:38

Hi all:

I am trying to write a report in a vendor db where they dump all of the Notes from anywhere in the system in one table. So for example, there are notes in the INV table. But instead of having a Notes field, there is a NOTES_ID record. That NOTES_ID record points to the NOTES.NOTES_ID table/record. Whenever you want to get the NOTES for the INV table, you have to join to the NOTES table. That works fine if you only want the NOTES for one table in your report.

We also have 2 sets of notes in the INV_LINES table. Discrepancy and Resolution. The same theory applies here. In the INV_LINES table there are two field called DISCR_NOTES_ID and RES_NOTES_ID. They too join to the NOTES table via the NOTES.NOTES_ID field.

So here lies my headache:

What do I do with my select statement and how do I join the NOTES, INV_LINE and INV tables and get all three of those NOTES to show up on my report?

INV.NOTES_ID = 868300
INV_LINE.DISCR_NOTE_ID = 868301
INV_LINE.RES_NOTES_ID = 868361

My head keeps going around in circles! I am thinking along the lines of declaring a variable, but still I can't seem to nail that down to anything concrete because it is so circular.

Because it is a vendor db, I cannot add anything to the database like a view.

Thanks for any help/hints!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-02 : 18:27:41

This is ugly, but should work

Jim

SELECT NOTES.NOTES
FROM
INV
INNER JOIN
NOTES
ON
INV.NOTES_ID = NOTES_NOTES_ID
UNION
SELECT NOTES.NOTES
FROM
NOTES
INNER JOIN INVL_LINES invl
ON
NOTES.NOTES_ID = invl.DISCR_NOTES_ID
OR
NOTES.NOTES_ID = invl.RES_NOTES_ID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 20:35:53

Not really a joke. You can use table alias and join the NOTES table multiple times


select *
from   INV i
       inner join INV_LINE l on i.<pk>          = l.<pk>
       inner join NOTES ni   on i.NOTES_ID      = ni.NOTES_ID
       inner join NOTES nld  on l.DISCR_NOTE_ID = nld.NOTES_ID
       inner joni NOTES nlr  on l.RES_NOTE_ID   = nlr.NOTES_ID

KH
[spoiler]Time is always against us[/spoiler]

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-07-04 : 15:36:36

Hi KHTAN:

Thank you for the great idea. Yes, of course. Aliases.

It is working like a charm!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources