By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

problem with union

P: 18
when i executed below query seperate they are displaying all the records and when i union these queries they are unable to display all the records.........

select 'deleted', m.col1,v.col2,m.col3 from MasterData m right join VendorData v on m.a=v.b where xyz in null

union

select 'new record',m.col1,v.col2,m.col3 from MasterData m left join VendorData v on m.a=v.b where xyz is null
Apr 16 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
when i executed below query seperate they are displaying all the records and when i union these queries they are unable to display all the records.........

select 'deleted', m.col1,v.col2,m.col3 from MasterData m right join VendorData v on m.a=v.b where xyz in null

union

select 'new record',m.col1,v.col2,m.col3 from MasterData m left join VendorData v on m.a=v.b where xyz is null
The first query will return all records from VendorData regardless if there are matching records from MasterData. The second query is the exact opposite. What records do you think are missing?

Try including the WHERE on your join condition.

-- CK
Apr 16 '08 #2

P: 18
select 'deleted', m.col1,v.col2,m.col3 from MasterData m right join VendorData v on m.a=v.b where xyz in null

union

select 'new record',m.col1,v.col2,m.col3 from MasterData m left join VendorData v on m.a=v.b where xyz is null




thanku ....................i found it.................what i did was i did not change the columns to be displayed for second query "new Records" becoz new records will be present in MasterData and also where clause
Apr 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.