Help with select query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with select query...

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-14 : 02:20:38

Hi

I have 3 tables,

Coulmns in table1

[START_TIME]
,[HOST_NAME]
,[RESOURCE_NAME]
,[JOB_STEP_INDEX]
,[JOB_STEP_ID]

columns in table2

resource
host_code

columns in table3

resource_pool
resource_pool_code

table 4(The table in which i need to enter the data)

[START_TIME]
,[HOST_NAME]
,[RESOURCE_NAME]
,[JOB_STEP_INDEX]
,[JOB_STEP_ID]
,[HOST_CODE]
,[RESOURCE_POOL_NAME]
,[RESOURCE_POOL_CODE]
,[RESOURCE]

I need to get data from table 1, table 2, table 3 and insert into table 4

The data from table1 is directly inserted into table 4 as it is ,and rest of the below 4 columns in table4 are left as NULLS,

[HOST_CODE]
,[RESOURCE_POOL_NAME]
,[RESOURCE_POOL_CODE]
,[RESOURCE]

Host_code is based on resource(in table 2), that is , each resource is associated with a resourc(in table 2)

and each resource_pool(in table3) is associated wih a resource_pool _code in the table 3

The logic behind updating ,[HOST_CODE]
,[RESOURCE_POOL_NAME]
,[RESOURCE_POOL_CODE]
,[RESOURCE] is
Host_name and Resource_name in table4 are the combination of resources and resources_pool in table 2 and table 3.........

So i need to categorize resource and resource_pool into appropriate columns in table 4 and update the code....

What i have been doing was , insert all the data from table 1 and then just update the resources and resource pools with the below update statements ,

--to update resource in table4

update table4
set table4.RESOURCE =
(SELECT Top(1)R.resource_name
FROM table2 AS R
WHERE R.resource_name = table4.host_name
OR R.resource_name = table4.resource_name)

---to update resource code in table4

UPDATE table4
SET table4.HOST_CODE =
(SELECT table2.HOST_CODE FROM table2
WHERE table4.RESOURCE = dbo.DW_T_ASW_HOST_CODE.RESOURCE_NAME)

--to update resource_pool in table 4

UPDATE table4
SET table4.RESOURCE_POOL_NAME =
(SELECT TOP(1)RP.resource_pool_name
FROM table3 AS RP
WHERE RP.resource_pool_name = table4.resource_name
OR RP.resource_pool_name = table4.host_name)

---- to update resource pool code in table4

UPDATE table4
SET table4.RESOURCE_POOL_CODE =
(SELECT table3.RESOURCE_POOL_CODE FROM table3
WHERE table4.RESOURCE_POOL_NAME = table3.RESOURCE_POOL_NAME)

This was working fine , but 2 things i need change now

1. instead updating the columns could any one please suggest me a way to join this 3 tables with complete select statement ?

so that just by running the insert-select statement i get all the data inserted into table 4

2. The column where there are no values for resource and resource pool names in table4 are nulls , i dont want any nulls in those two columns instead of NULL i need to have "notDefined"

So could any one please help me with writing a select sql query with all the above conditions....

Thanks in Advance.....

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-14 : 05:36:44

Try this:


INSERT INTO table4([START_TIME], [HOST_NAME], [RESOURCE_NAME], [JOB_STEP_INDEX], [JOB_STEP_ID],
                   [HOST_CODE], [RESOURCE_POOL_NAME], [RESOURCE_POOL_CODE], [RESOURCE])
   SELECT [START_TIME], [HOST_NAME], [RESOURCE_NAME], [JOB_STEP_INDEX], [JOB_STEP_ID],
          (SELECT T2.HOST_CODE
             FROM table2 AS T2
            WHERE T1.RESOURCE = dbo.DW_T_ASW_HOST_CODE.RESOURCE_NAME),
          ISNULL((SELECT TOP(1)RP.resource_pool_name
                    FROM table3 AS RP
                   WHERE RP.resource_pool_name = T1.resource_name
                      OR RP.resource_pool_name = T1.host_name), 'NotDefined'),
          (SELECT RP.RESOURCE_POOL_CODE
             FROM table3 AS RP
            WHERE RP.RESOURCE_POOL_NAME = T1.RESOURCE_POOL_NAME),
          ISNULL([RESOURCE], 'NotDefined')
     FROM (SELECT [START_TIME], [HOST_NAME], [RESOURCE_NAME], [JOB_STEP_INDEX], [JOB_STEP_ID],
                  (SELECT Top(1)R.resource_name
                     FROM table2 AS R
                    WHERE R.resource_name = T1.host_name
                       OR R.resource_name = T1.resource_name) AS [RESOURCE]
             FROM table1 AS T1) AS T1

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-14 : 15:09:09

Could you please explain the below syntax a bit??

As far as i can understand

SELECT [START_TIME], [HOST_NAME], [RESOURCE_NAME], [JOB_STEP_INDEX], [JOB_STEP_ID],

---- this is host_code
(SELECT T2.HOST_CODE
FROM table2 AS T2
WHERE T1.RESOURCE = dbo.DW_T_ASW_HOST_CODE.RESOURCE_NAME),

----this gives resource_pool_name

ISNULL((SELECT TOP(1)RP.resource_pool_name
FROM table3 AS RP
WHERE RP.resource_pool_name = T1.resource_name
OR RP.resource_pool_name = T1.host_name), 'NotDefined'),

----this gives resource pool code

(SELECT RP.RESOURCE_POOL_CODE
FROM table3 AS RP
WHERE RP.RESOURCE_POOL_NAME = T1.RESOURCE_POOL_NAME),

---- i cannot understand how we are getting resource here ??

ISNULL([RESOURCE], 'NotDefined')
FROM (SELECT [START_TIME], [HOST_NAME], [RESOURCE_NAME], [JOB_STEP_INDEX], [JOB_STEP_ID],

(SELECT Top(1)R.resource_name
FROM table2 AS R
WHERE R.resource_name = T1.host_name
OR R.resource_name = T1.resource_name) AS [RESOURCE]
FROM table1 AS T1) AS T1

quote:

Originally posted by malpashaa

Try this:


INSERT INTO table4([START_TIME], [HOST_NAME], [RESOURCE_NAME], [JOB_STEP_INDEX], [JOB_STEP_ID],
                   [HOST_CODE], [RESOURCE_POOL_NAME], [RESOURCE_POOL_CODE], [RESOURCE])
   SELECT [START_TIME], [HOST_NAME], [RESOURCE_NAME], [JOB_STEP_INDEX], [JOB_STEP_ID],
          (SELECT T2.HOST_CODE
             FROM table2 AS T2
            WHERE T1.RESOURCE = dbo.DW_T_ASW_HOST_CODE.RESOURCE_NAME),
          ISNULL((SELECT TOP(1)RP.resource_pool_name
                    FROM table3 AS RP
                   WHERE RP.resource_pool_name = T1.resource_name
                      OR RP.resource_pool_name = T1.host_name), 'NotDefined'),
          (SELECT RP.RESOURCE_POOL_CODE
             FROM table3 AS RP
            WHERE RP.RESOURCE_POOL_NAME = T1.RESOURCE_POOL_NAME),
          ISNULL([RESOURCE], 'NotDefined')
     FROM (SELECT [START_TIME], [HOST_NAME], [RESOURCE_NAME], [JOB_STEP_INDEX], [JOB_STEP_ID],
                  (SELECT Top(1)R.resource_name
                     FROM table2 AS R
                    WHERE R.resource_name = T1.host_name
                       OR R.resource_name = T1.resource_name) AS [RESOURCE]
             FROM table1 AS T1) AS T1

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-15 : 08:05:51

Because you need resource value in the query of host_code. So I used derived table (In the FROM clause) to get the resource value, and then I used it as a column in the outer query.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-15 : 11:50:47

I think it is much easier to understand if you provide sample data and expected result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources