Solved

SQL Join Query question

Posted on 2011-09-12
7
255 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:ewangoya
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:ewangoya
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Accepted Solution

by:
ewangoya 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:ewangoya
ID: 36526859

That should work
0
 

Author Closing Comment

by:RD11
ID: 36532913
Thank you
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

937 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now