Query Help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2012-10-29 : 14:40:44

I need to pull the data from two tables called Table1 & Table2. from these i need Servername, Application ID also I need to add one extra coulmn called 'Appserver'. This coulmn should be represented as

Table1 if 'Servername', 'Application ID' from Tabe1

or

Table2 if 'Servername', 'Application ID' from Tabe2

or

Both Table1 if 'Servername', 'Application ID' from Tabe1 & Table2

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 14:44:48

[code]
SELECT Servername, [Application ID],
CASE WHEN MIN(AppServer) <> MAX(AppServer) THEN 'Both' ELSE MIN(AppServer) END AS AppServer
FROM
(SELECT Servername, [Application ID] ,CAST('Table1' AS varchar(6)) AS AppServer
FROM table1
UNION ALL
SELECT Servername, [Application ID],'Table2'
FROM table2
)t
GROUP BY Servername, [Application ID]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2012-10-29 : 15:19:37

Thank you very much for your help.

But Sorry, I am not getting the result. I will explain one more time clearly. Please check it once and let me know if I am confusing you.

I need to pull the data from two tables called Table1 & Table2.

Table1 has following columns:
ServerName ApplicationName ApplicationID
Table2 has following columns:
ServerName ApplicationName ApplicationID

From these I need Servername, ApplicationID and one extra coulmn called 'Appserver'. Which is not present in the table.

This column should be appeared as

Table1 if 'Servername', 'ApplicationID' pulled only from Tabe1
or
Table2 if 'Servername', 'ApplicationID' pulled only from Table2
or
Combine if 'Servername', 'ApplicationID' pulled from only both tables (table1& Table2)

Thanks

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2012-10-29 : 15:46:43

Can any one help me plzz

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-29 : 16:30:27

What Visakh posted seems to be exactly what you are asking. Can you post representative sample data in the tables and the output you are expecting to see?

aaronwc
Starting Member

2 Posts

Posted - 2012-10-29 : 16:33:31

Are you looking for something like this?

SELECT T1.Servername, T1.ApplicationID, 'Combine' as AppServer
FROM Table1 T1
INNER JOIN Table2 T2 on (T1.ServerName = T2.ServerName and T1.ApplicationID = T2.ApplicationID)
UNION
SELECT T1.Servername, T1.ApplicationID, 'Table1' as AppServer
FROM Table1 T1
LEFT JOIN Table2 T2 on (T1.ServerName = T2.ServerName and T1.ApplicationID = T2.ApplicationID)
WHERE T2.ServerName IS NULL
UNION
SELECT T2.Servername, T2.ApplicationID, 'Table2' as AppServer
FROM Table2 T2
LEFT JOIN Table1 T1 on (T2.ServerName = T1.ServerName and T2.ApplicationID = T1.ApplicationID)
WHERE T1.ServerName IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 16:36:27

quote:
Originally posted by aswindba1

Thank you very much for your help.

But Sorry, I am not getting the result. I will explain one more time clearly. Please check it once and let me know if I am confusing you.

I need to pull the data from two tables called Table1 & Table2.

Table1 has following columns:
ServerName ApplicationName ApplicationID
Table2 has following columns:
ServerName ApplicationName ApplicationID

From these I need Servername, ApplicationID and one extra coulmn called 'Appserver'. Which is not present in the table.

This column should be appeared as

Table1 if 'Servername', 'ApplicationID' pulled only from Tabe1
or
Table2 if 'Servername', 'ApplicationID' pulled only from Table2
or
Combine if 'Servername', 'ApplicationID' pulled from only both tables (table1& Table2)

Thanks

This is exactly what my suggestion does!
Please use it as suggested and you'll get output
In case you're facing any error please post what error message is
if your table structure or scenario is different please post sample data to show that
Without any of above we cant guess whats happening at your end without seeing your system/data and suggest any soultion!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2012-10-29 : 20:00:21

Thank you ver much...I am looking to modify the query as below

select distinct t1.ServerName, t1.[Application ID], t1.[Application Name], t2.VP1, T2.VP2, T2.SVP, T2.[DC2015 Move Group], T1.[System Type],
T3.Line, AppServerSource = (Select IsNull(t1.ServerName,t2.ServerName) as ServerName,
IsNull(t1.[Application Name],t2.[Application Name]) as [Application Name],
Case
When t1.[Application Name] is null then 'Check'
When t2.[Application Name] is null then 'Payorder'
Else 'Both'
End AppServer)
from Table1 t1 INNER JOIN Table2 t2
on t1.[Application ID]= t2.[Application ID] and t1.Servername = t2.ServerName JOIN BIM T3
ON t1.[Application Name]= t3.[Application Name]
order by Servername

After executing the above query I am getting the follwoing error.

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 20:07:16

Its not even close to what I suggested!

Post some sample data and explain what you're trying to achieve
Unless you do that I'm not going to help you on this further as scenario seems to be completely different from what you explained initially.
see guidelines on how to post data here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources