Simple Inner Join

[#1]

Hello,

Says i have the following 2 tables, to describe which zone a country belongs to...

Country
Ctry_Code, Ctry_Desc

Zone
Zone_Code, Zone_Desc, Ctry_Code

How can i write a simple inner join to for this 2 tables?

[prieper · Accepted solution]

QV links the tables automatically by associating the same fieldnames, thus you only need

Country: LOAD * FROM YourCountry; Zone: LOAD * RESIDENT YourZone;

.

If you are really up to an inner join, you need to specify this:

Country: LOAD * FROM YourCountry; INNER JOIN (Country) LOAD * RESIDENT YourZone;

.

The (Country) is not really necessary, but I prefer to make a proper reference.

HTH
Peter

[#3]

Thanks Peter...

What about "SQL SELECT" ?

I always use LOAD and SQL SELECT together....

LOAD ....,
.....,
SQL SELECT *
FROM .....

and also, instead of using RESIDENT.

I did someting like...

LOAD ctry_code, ctry_desc, zone_code, zone_desc;
SQL SELECT *
from country inner join zone on country.ctry_code = zone.ctry_code;

[prieper]

These are just indicators, what kind of file it is. When you load straight from SQL-database, the syntax will be LOAD * SQL SELECT ...., if the table is already loaded in memory: LOAD ... RESIDENT ..., if from internal table: LOAD ... INLINE, if from qvd etc.

When the tables are in the same database, you may create your JOINS directly in SQL. This is mainly a question of the load on the production-server and your philosophy on datawarehouse (we try to download each table from SQL separately and then make most of the JOINs in the applications).

Peter