?
Solved

SQL Join Query question

Posted on 2011-09-12
7
Medium Priority
?
260 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 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