compare on multiple 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 on multiple columns

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-14 : 13:10:10

I know I’m syntactically fubar on this, but you should be able to see what I’m trying to do. What approach do I need to take with this?:

SELECT * FROM DOUGLASMNI.dbo.MNI t1
WHERE FNAME AND MNAME AND LNAME IN (SELECT FNAME AND MNAME AND LNAME FROM MNI.dbo.mni t2 WHERE t2.FNAME=t1.FNAME AND T2.MNAME=T1.MNAME AND T2.LNAME=T1.LNAME)

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 13:14:51

[code]

SELECT *
FROM DOUGLASMNI.dbo.MNI t1
INNER JOIN MNI.dbo.mni t2
ON t1.FNAME = t2.FNAME
AND T1.MNAME = T2.MNAME
AND T1.LNAME = T2.LNAME

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-14 : 13:22:17

at'll do. thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 13:44:42

might result in varied record count from original query if relationship between tables is one to many (ie multiple records existing in MNI.dbo.mni with same FNAME ,MNAME ,LNAME combinations
so I prefer EXISTS check


SELECT * FROM DOUGLASMNI.dbo.MNI t1
WHERE EXISTS(SELECT 1 FROM MNI.dbo.mni t2 WHERE t2.FNAME=t1.FNAME AND T2.MNAME=T1.MNAME AND T2.LNAME=T1.LNAME)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 13:47:40

How is that going to allow a compare?

If the tables have the same number of Columns



	SELECT 'DOUGLASMNI.dbo.MNI' AS SOURCE, * 
	  FROM DOUGLASMNI.dbo.MNI
UNION ALL
	SELECT 'MNI.dbo.mni' AS SOURCE,* 
	  FROM MNI.dbo.mni
ORDER BY....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 13:50:48

quote:
Originally posted by X002548

How is that going to allow a compare?

If the tables have the same number of Columns

SELECT 'DOUGLASMNI.dbo.MNI' AS SOURCE, *
FROM DOUGLASMNI.dbo.MNI
UNION ALL
SELECT 'MNI.dbo.mni' AS SOURCE,*
FROM MNI.dbo.mni
ORDER BY....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Unless I miss something I dont think OP specified anything re. comparing of values.
I thought the original query posted was trying to use MNI.dbo.mni as a kind of lookup to see if details which existed in MNI.dbo.mni was getting retrieved

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 13:55:44

I took the title to mean compare, not join

my bad

Still Mr. Hamel..you will notice that there are many different wqays to do things in SQL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 13:57:17

quote:
Originally posted by X002548

I took the title to mean compare, not join

my bad

Still Mr. Hamel..you will notice that there are many different wqays to do things in SQL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Ok that is also a fair conclusion
Now we can wait till OP comes back with what he's exactly after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources