How to display unmatched records in two tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to display unmatched records in two tables

Author

Topic

eljor
Starting Member

1 Post

Posted - 2008-02-28 : 13:45:38

I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.

SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
-------------------------------------------
Result: Record count 3000.

Only field3 is populated everything else is null.

select field3 from tbl1 group by field2 - record count = 48

SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL2

-------------------------------------

Result Record count = 0

------------------------------------------------------
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
minus
SELECT
nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM tbl2
---------------------------
Result: Record Count = 48

I used the left join and it didn't work.

I would like to build a query that will display all 3000 records.

Any help will be appreciated.

thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 16:45:05

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-02-28 : 16:45:56

What is nvl?

Can you provide a sample set of data and the create statements for both tables?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-29 : 05:58:36

Post your question at Oracle Forums
www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-03-03 : 20:05:19

FYI: nvl == IsNull

nvl (Null Value?) is Oracle...
IsNull is Microsoft...
coalesce is SQL...

=======================================
Society is like a stew. If you don't keep it stirred up you get a lot of scum on the top. -Edward Abbey, naturalist and author (1927-1989)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources