[SOLVED] Unique table list from multiple tables

What would be the easiest way to (without creating a new database table) create a single table with a unique list of account numbers from multiple tables that I can use to then left outer join my multiple tables on to.

In other words I have 3 tables for example that all have the following Account1 as a field and a value field.

Now in Table 1, Account1 has a b and c and in Table 2 the field has a b and d and in Table 3 a and c only.

Now I want to join them so that I have Account1 showing a b c and d (without duplicates) and then use this to left outer join tables1 2 and 3's values to this unique list.

(Bearing in mind that my unique list is not just account1 it is actuall account 1,2,3,4,and 5! So may have to create template table for this!)

Any help is appreciated.

I have tried doing a select distinct

This message has been edited. Last edited by: Michael_86,

Webfocus 7.7.03
Windows
Excel

Posts: 21 | Registered: February 08, 2013

FrankDutch

Virtuoso

posted

Hide Post

It does depend if your source is a SQL or focus format file and if the second step where you want to join this first result with is also focus or SQL.
You can create a union select in SQL or you can do more or less the same in focus.
You say not to be willing to create a new table, but in fact you do that even if it is a temporary one.
If you select the data from the first table you can do this ...

TABLE FILE ONE
BY ACCOUNT1
BY FAAAA 
ON TABLE HOLD AS HOLDA
END
TABKE FILE TWO
BY ACCOUNT1
BY FAAAA
ON TABLE HOLD AS HOLDB
END
...and so on...
Then...
TABLE FILE HOLDA
BY ACCOUNT1
BY FAAAA
ON TABLE HOLD AS FINALHOLD FORMAT FOCUS INDEX ACCOUNT1
MORE
FILE HOLDB
...
END

This should give you the unique selection of account1 with the fields

If you want to join this intermediate result with an other table you need to add an index to the hold file
That is what I did in the last hold statement

I hope this is what you want

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7

Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006

Michael_86

Member

posted

Hide Post

Hi Frank,

Thanks for the reply, that worked perfectly for what I needed, but I didnt need to link back unique list as in the end I created a new table that I uploaded as a master file to link to directly. But I have used this already in a different report. So thank you for your help.

Webfocus 7.7.03
Windows
Excel

Posts: 21 | Registered: February 08, 2013

Ignored post by Michael_86 posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

[SOLVED] Unique table list from multiple tables

Focal Point | Privacy Statement | Terms of Service