Querying association where more then one can exist

[eccl_support]

Hi,

I'm trying to write a query which will return all diagrams which do not have a certain type of note on them.  We've started adding 'Documentation' notes to diagrams to demonstrate the status a diagram is at, i.e Draft or Signed Off.

I've written the query below to identify diagrams where these do not exist, but I'm having a problem where diagram have multiple notes on them.

Select [Note] Into@docnoteWhere [Type] And [Type].[Name] ="Documentation"

Select [Diagram] Where [Drawing Note] Notin@docnote

Historically, we've added notes to diagram to record issues or questions we have with a process.  And when I run the query above, it returns diagrams which contain both these notes.

Ultimately, I only want a list of diagrams which do not have 'Documentation' notes regardless of whether they have other notes on.

Does anyone have a solution for this?

Many Thanks

[SVanSchoonlandt]

I do think your query will work if you drop the first [Type] after the select

Select [Note] Into@docnoteWhere [Type] And [Type].[Name] ="Documentation"

Select [Diagram] Where [Drawing Note] Notin@docnote

shouuld be

Select [Note] Into@docnoteWhere [Type].[Name] ="Documentation"

Select [Diagram] Where [Drawing Note] Notin@docnote

you could also do

Select [Note] Into@docnote Where [Type].[Name] =&name

Select [Diagram] Where [Drawing Note] Notin@docnote

like that when executing the query he will prompt you to specify the type and you are able to reuse the query for different type of notes.

Also the query from yannick is slightly easier and probably more performant.

[yrudloff]

Hi,

Normally you should be able to have the result wanted with (Even if you have manu notes on a diagram) :

Select [Diagram] WhereNot [Drawing Note].[Type] "Documentation"

Let me know if there is something more complex,

yannick