How to use a table without referencing it

[Nitibob]

Hi

I have got a table (table_x) with a list of Orders No. It is a temporary table that is a subset of a bigger list.

For one set of reporting requirements, I have joined it to another table (table_y) where I want to see rows that have the Order No from table_x.

so the join is
table_x.order No. = table_y.order No.

This works fine, but for another set of requirements, I need to use the list in table x as an exclusion list.

I have tried to modify the join above so:
table_x.order No. != table_y.order No.

but this doesn't seem to work..as you don't pull table_x.order No. into the report.

Has anyone got any ideas?

Thanks

 

[blom0344]

Hello Nitibob,

Exclusion is best attained by using Combining queries using Set operators. Set operators in SQL are UNION, UNION ALL, EXCEPT ALL, INTERSECT , INTERSECT ALL.

BO lets you use these by first creating a query and pushing the 'Combine query' button. This gives a second tab in query panel. Choose the '-' option by clicking on the 'U' (= union) default. The set generated by the second query is subtracted from the set retrieved by the first.

I think this will give you a handle to succeed in getting the exclusion effect.

Note: EXCEPT operator takes into account the order of queries: A except B is something else then B except A. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Nitibob]

Thanks for the response blom0344

The use of a Minus combination query worked great.