Solved

SQL Join Query question

Posted on 2011-09-12
7
258 Views
Last Modified: 2012-05-12
Hi,

I have the below query that works on one column, then creates a table from the result that gets.

I need a query that checks 3 columns then creates the table from the result.

I need to check for any change for the columns [ps].ASE, [ps].Env, [ps].City

Thank you,
RD11
SELECT [ps].ETag, [ps].Host, [ps].[Private Input], [ps].[From ci db], [ps].[ID-PK], [ps].Src, [ps].ASE, [ps].Env, [ps].EnvD, [ps].Cls [ps].City, [ps].[Surpluss EST], [ps].[Monthly Cost], [ps].Description, [ps].[Install Date], [ps].[Purchase Date], [ps].Env, [ps].Pool, [ps].Billed, [ps].Price, [ps].[C Org], [ps].[Resp], [ps].Function,[ps].Comments, [ps].[Last Update], [ps].OS, [ps].Status INTO Diff_table
FROM [ps] LEFT JOIN cI_table ON [ps].Host = cI_table.HOST
WHERE (((cI_table.HOST) Is Null));

Open in new window

0
Comment
Question by:RD11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36526526

Checks the three columns exactly for what?

If they are different in the diff table, you can use exists
SELECT [ps].ETag, [ps].Host, [ps].[Private Input], [ps].[From ci db], [ps].[ID-PK], [ps].Src, [ps].ASE, [ps].Env, [ps].EnvD, [ps].Cls [ps].City, [ps].[Surpluss EST], [ps].[Monthly Cost], [ps].Description, [ps].[Install Date], [ps].[Purchase Date], [ps].Env, [ps].Pool, [ps].Billed, [ps].Price, [ps].[C Org], [ps].[Resp], [ps].Function,[ps].Comments, [ps].[Last Update], [ps].OS, [ps].Status 
INTO Diff_table
FROM [ps] 
LEFT JOIN cI_table ON [ps].Host = cI_table.HOST
where not exists (select 1 from Diff_table B
                  where [ps].ASE = B.ASE and [ps].Env = B.Env and [ps].City = B.City)
and (((cI_table.HOST) Is Null));

Open in new window

0
 

Author Comment

by:RD11
ID: 36526530
If any change in either any one of them.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36526541

This line will get the rows that are not in the diff table based on the three columns

where not exists (select 1 from Diff_table B where [ps].ASE = B.ASE and [ps].Env = B.Env and [ps].City = B.City)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 36526548
You will actually need  a primary key for all this to work properly, so assuming the primary key is ETag

where not exists (select 1 from Diff_table B where [ps].ETag = B.ETag and [ps].ASE = B.ASE and [ps].Env = B.Env and [ps].City = B.City)
0
 

Author Comment

by:RD11
ID: 36526633
I actually DO NOT have a primary Key, I am using the Host name as a PK.  Can I still do that?
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36526859

That should work
0
 

Author Closing Comment

by:RD11
ID: 36532913
Thank you
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question