Comparing Data - 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

Comparing Data

Author

Topic

zhel04
Starting Member

38 Posts

Posted - 2011-11-16 : 02:57:33

How to query to get data with two conditions in one output.
Conditions:
1. idno existing in Table1 and idno not existing in Table2
2. Tbl1 idno = Tbl2 idno AND Tbl1 base <> Tbl2 base

Example:

Tbl1 Tbl2

idno base idno base
123 50 123 80
456 10 456 10
789 20 789 50
101 30 101 30
102 40 104 50
103 100 105 90

OUTPUT:
idno base
123 50
789 20
102 40
103 100

Note: The data for output will come from Tbl1

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 03:25:31

[code]
SELECT t1.*
FROM Tbl1 t1
LEFT JOIN Tbl2 t2
On t2.idno = t1.idno
AND t2.base = t1.base
WHERE t2.idno IS NULL

or

SELECT *
FROM Tbl1 t1
WHERE NOT EXISTS(SELECT 1 FROM Tbl2 WHERE idno = t1.idno AND base = t1.base
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources