Solved

Query to look in two columns in a table for the same value

Posted on 2010-11-30
8
251 Views
Last Modified: 2012-05-10
Hello. I have a custom MS SQL 2005 SP3 database used in SCCM 2007 OSD deployments. The database contains a table that has four columns. The columns are computer name, old computer name, mac address and date name was changed.

I am trying to write a query that looks at the values in the computer name column and  the value name in the old computer name column, if the same value is in both colums it outputs it. The value will not be on the same row so I cannot do a match.

For example, I have a machine called Computer1. It is both the computername and oldcomputername columns. I want the query to output all values for both rows that it is in. Is this possible?

Below is my query to pull in everything from the table.

SELECT *
FROM MachineInformation
0
Comment
Question by:Lorrec
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34242219
hi,

sory but your Q is not much clear.

SELECT *
FROM MachineInformation
WHERE Computername=oldcomputername

what you want exactly
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 34242305
Sounds like you want something like this:

SELECT *
FROM MachineInformation MI
   INNER JOIN MachineInformation MI_Old ON MI.ComputerName = MI_Old.oldcomputername
0
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 34242555
Or are you looking just for this?

select * from MachineInformation
 where [computer name] = 'Computer1'
 or [old computer name] = 'Computer1'
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.

 

Author Comment

by:Lorrec
ID: 34242645
Thanks for the response. Ihat is what I needed.
0
 

Author Closing Comment

by:Lorrec
ID: 34242652
Thank you for the help.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34245125
Hi Shaun.

what is the difference between my query and ur query???
can you please explain??
0
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 34247388
Brichsoft -

Your query would only return rows where both the computername column and oldcomputername matched in the same row.

The inner join Shaun did would return every row in the table where the computername column and oldcomputername matched regardless of if they originally existed in the same row.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34247402
Hey...

Thanks a lot sir.....

somehow,my mind was stop thinking....

Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql calculate reminders 11 76
Table create permissions on SQL Server 2005 9 42
INSERT DATE FROM STRING COLUMN 18 56
Query to return total 6 19
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard 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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

832 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