Link to home
Start Free TrialLog in
Avatar of Gary Croxford
Gary CroxfordFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Compare two tables and convert NULL values to zero


I have two tables showing inventory movement transactions across a network. Table 1 (tblSent) contains date, job no and a count of the part numbers associated with the job no. Table 2 (tblReceived) contains Date, Job No and a count of the part numbers associated with the jobn no.

Some of the transactions are getting lost across the network and I want to identify those.

I want to build a query that compares the two tables giving date, job no, number of parts sent, number of parts received.

I want the result to feature all lines from tblSent and where that job no doesn't appear in tblReceived for the query to return a zero

Any advice gratefully received,
Avatar of chapmandew
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
select * from tblsent where jobno is NULL
select * from tblreceived where jobno is NULL

The above should provide you with data where the jobno is NULL.

select * from tblsent where jobno not in (select jobno from tblreceived)

The above will return rows from tblsent where there is no corresponding entry in tblreceived.