Solved

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

Posted on 2010-11-30
8
234 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
 

Author Comment

by:Lorrec
ID: 34242645
Thanks for the response. Ihat is what I needed.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

20 Experts available now in Live!

Get 1:1 Help Now