Multiple columns referring to same table

[#1]

Hey all,

I'm having the following situation:

I have 2 tables in SQL, the first table contains faults and looks like this: (only shown related columns)

faultId	Description	createdById	modifiedById
001	description001	userId001	userId002
002	description002	userId003	userId003
003	description003	userId004	null

the second contains users and looks like this:

userId	username	emailAddress
userId001	daveP	davep@mail.com
userId002	peterK	peterk@mail.com
userId003	fredC	fredc@mail.com

Now I can't name the columns in such a way that it would get connected automatically, as that would require me to name both createdById and modifiedById to 'userId' (right?).

How can I connect these tables?

Regards,

[alexandros17]

load twice the second table:

load userid as createdbyid, username as usernamecreate, emailaddress as emailcreate resident ...

load userid as modifiedbyid, username as usernamemodif, emailaddress as emailmodif resident ...

[jonathandienst]

Hi

If the userid table contains only a few fields (eg id, username and email address as per your post), then you can map these into the main table:

mapUserIdName:

Mapping Load userId, username

From Users;

mapUserIdEmail:

Mapping Load userId, emailAddress

From Users;

Faults:

LOAD faultId,

     ...

     createdById,

     Applymap('mapUserIdName', createdById) As createdByUsername,

     Applymap('mapUserIdEmail', createdById) As createdByEmail,

     modifiedById,

     Applymap('mapUserIdName', modifiedById) As modifiedByUsername,

     Applymap('mapUserIdEmail', modifiedById) As modifiedByEmail,

     ....

From Faults;

HTH

Jonathan

[ychaitanya]

use lookup function in the first table..

load second table first

then while loading the first table ... use look up function on the previous loaded table..

OR

Use join

Thanks

Chaitanya

[#5]

Thanks for your answer!

I suppose that would do. However this creates duplicate data. The user table is not that big so it won't really decrease performance that much. Still I'd think there would be a more elegant solution (?).

[#6]

This seems the best solution, however I can not get the syntax right. I am using a SQL database, and have incorparated your part of the code in the following way:

mapUserIdName:

Mapping SQL SELECT Id, Username

From "myDataBase".dbo.Users;

mapUserIdEmail:

Mapping SQL SELECT Id, EmailAddress

From "myDataBase".dbo.Users;

Faults:

SQL SELECT ContractorId,

  CreatedAt,

  CreatedById,

  Applymap('mapUserIdName', createdById) As createdByUsername,

  Applymap('mapUserIdEmail', createdById) As createdByEmail,

  ModifiedAt,

  ModifiedById,

  Applymap('mapUserIdName', modifiedById) As modifiedByUsername,

  Applymap('mapUserIdEmail', modifiedById) As modifiedByEmail,

    Description as FaultDescription,

    Id as FaultId

FROM "myDataBase".dbo.Faults;

I recieve the following error:

ErrorMsg: 'Applymap' is not a recognized built-in function name.

How should I apply the mapping instead?

[jonathandienst]

Hi

Everything from the SQL command to the closing ; is sent to the DBMS. Applymap is a QV function, not a SQL function, so you need something like this:

Faults:

LOAD *,

  Applymap('mapUserIdName', createdById) As createdByUsername,

  Applymap('mapUserIdEmail', createdById) As createdByEmail,

  Applymap('mapUserIdName', modifiedById) As modifiedByUsername,

  Applymap('mapUserIdEmail', modifiedById) As modifiedByEmail;

SQL SELECT ContractorId,

  CreatedAt,

  CreatedById,

  ModifiedAt,

  ModifiedById,

  Description as FaultDescription,

  Id as FaultId

FROM "myDataBase".dbo.Faults;

HTH

Jonathan