Solved

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

Posted on 2010-11-30
8
268 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
[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
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

695 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