hi all

I wondered if someone could assist me a little

I have this code working to join multiple tables together.

select *, 'ALL' AS COMPANY
from dbo_SALFLDGALL
UNION ALL
select *, 'APL' AS COMPANY
from dbo_SALFLDGAPL
UNION ALL
select *, 'CPL' AS COMPANY
from dbo_SALFLDGCPL
UNION ALL
select *, 'ASL' AS COMPANY
from dbo_SALFLDGASL

One of the fields in the above tables is ACCNT_CODE.

I want to lookup the ACCNT_NAME from dbo_SSRFACC (another table) where the COMPANY = SUB_DB in dbo_SSRFACC but wouldn't know how to modify the above code.

I also want to lookup another field name from the above tables called ANAL_T8 which is NAME where COMPANY from the SALFLDG tables = SUN_DB from dbo_SSRFANV but also where CATEGORY field in dbo_SSRFANV = "T8"

I hope someone can assist me in making this change to my code.

Thanks very much

SW

If I understand this correctly, this could be a way:

; WITH u AS (
select *, 'ALL' AS COMPANY
from dbo_SALFLDGALL
UNION ALL
select *, 'APL' AS COMPANY
from dbo_SALFLDGAPL
UNION ALL
select *, 'CPL' AS COMPANY
from dbo_SALFLDGCPL
UNION ALL
select *, 'ASL' AS COMPANY
from dbo_SALFLDGASL
)
SELECT acc.ACCNT_NAME, u.*
FROM u
JOIN dbo_SSRFACC ACC ON u.ACCNT_CODE = ACC.ACCNT_CODE
AND ACC.COMPANY = 'SUB_DB'

but to be honest I am not sure that I understand narrative fully.

And here I get lost completely.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx