SQL Question

[former_member2987]

Hi folks,

I need to create a lookup from idmv_value_ext where I show the displayname and company code on the same line.

I've come up with this query that presents the correct data but does not put it on the same line.

select searchvalue, attrname from idmv_value_ext where (attrname='DISPLAYNAME' or attrname='MX_FS_COMPANY_CODE_ID') and  mskey in

(select mskey from idmv_value_ext where attrname='MX_FS_JOB_ID' and SearchValue in (50029231,50011466,50000597,50050904))

Does anyone know how I can accomplish this?

Thanks,

Matt

[Former Member · Accepted solution]

Hi Matt,

Try this:

select t1.searchvalue as DisPlayname, t2.searchvalue as CompanyCode

from idmv_value_ext t1 inner join idmv_value_ext t2 on t1.mskey = t2.mskey

where t1.attrname='DISPLAYNAME' and t2.attrname='MX_FS_COMPANY_CODE_ID'

and  t1.mskey in (select mskey from idmv_value_ext where attrname='MX_FS_JOB_ID' and SearchValue in (50029231,50011466,50000597,50050904))

(I did not test it)

Regards,

Ole K.

[former_member2987 · Accepted solution]

Thanks, Ole, that seems to do it. I assume I can keep extending this if I need additional attributes?

Matt

[Former Member · Accepted solution]

Hi Matt,

Sure you can extend this, but it will of course only work for single-value attributes.

Ole K.

[devaprakash_b]

Hi Matt,

The below query can be extended with adding joins for adding other attributes too.

select displayname.searchvalue, displayname.attrname, MX_FS_COMPANY_CODE_ID.searchvalue, MX_FS_COMPANY_CODE_ID.attrname

from idmv_value_ext [displayname] with (nolock)

join idmv_value_ext [MX_FS_COMPANY_CODE_ID] with (nolock)
on [displayname].mskey = [MX_FS_COMPANY_CODE_ID].mskey

join idmv_value_ext [mskeyfilter] with (nolock)
on [displayname].mskey = [mskeyfilter].mskey

where displayname.attrname = 'displayname' and MX_FS_COMPANY_CODE_ID.attrname = 'MX_FS_COMPANY_CODE_ID'
and [mskeyfilter].attrname='MX_FS_JOB_ID' and [mskeyfilter].SearchValue in ('50029231','50011466','50000597','50050904')

Regards,

DP