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
2  978 
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
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 Sign in to post your reply or Sign up for a free account.
Similar topics
by: Stephen Miller |
last post by:
Firstly, sorry for the long post, but I've included a fair bit of
sample data.
Im doing a comparision of 10yr Bond prices and CPI adjustments, with
an 18 week moving average of the CPI. I'm...
|
by: Bill |
last post by:
I've created this:
SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL
|
by: Dave |
last post by:
I am working with a proprietary database that records the date, time,
location, and speed of a vehicle. It is pulling this information from
GPS unit tied to a vehicle. The table is populated with...
|
by: DarthMacgyver |
last post by:
Hello,
I recently wrote a survey application. Each question is very similar.
The first questions gives me a problem when there are multiple people
taking the survey (The Database connection...
|
by: Thomas |
last post by:
Hi,
I implemented a composite pattern which should be serializable to xml.
After spending some time in the newsgroups, i finally managed
serializing, even with utf-8 instead of utf-16, which...
|
by: Gerry |
last post by:
I am relatively new to DB2 and having a problem with a simple union
statement.
Running Db2UDB version 8.1.1 on Aix 5.1
The union and union all SQL statements I am running produce the same...
|
by: hubmei75 |
last post by:
Hello,
I have a simple table containing adresses.
A sample view of the table is
id name city
--------------------------------
100 Meier New York
101 Meier Tokyo
110 ...
|
by: 肥權 |
last post by:
Hi everybody,
I have a table storing the test dates, student names and the grade of
the students. I am now going to find out those dates with student1 got
A and student2 got B and vice versa...
|
by: google |
last post by:
Hi
Hope someone can help me with this - have been staring at this problem
all day, and with the cold I've got, just don't seem to be able to
figure it out!
I've got two tables, here with some...
|
by: SRK |
last post by:
Hi,
I wanted to use an anonymous union within an structure something like
below -
struct Test
{
union
{
std::string user; //char user;
std::string role; //char role;
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
