compare columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

compare columns

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2013-10-23 : 17:09:57

Hi,

I don't know but I struggle today:(

I have one table with the following columns:

ID.......Name
12345.....test
23456.....test
99922.....test1

I have the ID of one object (example: 12345). Now I want the ID of the elememnt with the same name as the object with ID 12345.

IN my example the output must be 23456.

I tried this:

SELECT *

FROM

dtree A4

WHERE

A4.Name

IN

(SELECT Name FROM dtree A3 WHERE A3.dataID = '7886825')

but I get more than one row as output...what i am missing?:)

KInd regards,

Lara

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 17:47:21

For your sample data, this:

SELECT a4.* FROM dtree
WHERE name IN (SELECT name FROM dtree a4 WHERE a3.dataId = '7886825')
AND id <> '7886825';

If there are other cases that do not work as you expect, can you post representative sample data?

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2013-10-23 : 18:25:42

Hi,

thanks for your answer. I think not everything is right in you statement beacuse you esed the alias a3 which is not defined. I tried to rewrite it as follows:

SELECT a3.* FROM dtree a3

WHERE a3.name IN (SELECT a4.name FROM dtree a4 WHERE a4.dataId = '7886825')
AND a3.dataid <> '7886825'

unfortunately i get the same result as in my initial post:(

can you give me a new hint?:)

Kund regards,

Lara

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-23 : 22:04:28

what does this return to u?
SELECT Name FROM dtree A3 WHERE A3.dataID = '7886825'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources