Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Join Query question

Posted on 2011-09-12
7
Medium Priority
?
263 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
  • 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
Industry Leaders: 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

577 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