sql statement help

[s***@gmail.com]

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

[Erland Sommarskog]

Post by s***@gmail.com
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.

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.

Post by s***@gmail.com
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"

And here I get lost completely.