?
Solved

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

Posted on 2010-11-30
8
Medium Priority
?
273 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 2000 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

764 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