At 03:20 PM 6/11/2006, you wrote:

>Hi,
>
>I have a situation where I need to find the intersection of two or
>more tables.
>
>For example,
>
>create table pn (u1 char(30), u2 char(30), value numeric(3,3));
>create table cw (u1 char(30), u2 char(30), value numeric(3,3));
>
>I need to find the set of (u1, u2) where it appears in both tables.
>
>pn and cw tables are experimental data from external sources.
>
>For each tuple of (u1, u2) in both tables, the "value" may be
>different but that is not the issue.
>
>What is the best indexing scheme and method for doing this intersection?
>
>My current way is
>select pn.u1, pn.u2 from pn join cw on pn.u1=cw.u1 and pn.u2=cw.u2;
>
>Are there better means?

Looks OK from the POV of logic. Can you explain how you want it to
be better? If it's a performance option you are seeking, you could
look at passing the search on the u2 match out to a WHERE clause, viz.

select pn.u1, pn.u2 from pn join cw on pn.u1=cw.u1
where cw.u2 = pn.u2

It might go either way, or might make no difference to the plan at all. :-)

./heLen

>Thanks
>Cheers
>maurice
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://www.firebirdsql.org and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>