We help IT Professionals succeed at work.

remove invalid data

259 Views
Last Modified: 2012-05-05
I have records for 2 fields (orderno and planningjob) that are held in the same table (binding).

I have attached a spreadsheet that is the result of:

select orderno, planningjob from binding
order by orderno

As you can see, some orderno are duplicated and some have a planningjob record associated.
Some orderno have a planningjob associated for one record but then not for another. (See Orderno 17. There are 5 records for it but only 2 have a planningjob associated).

I need to be able to only show records that don't have any planningjob associated at all
Binding.xls
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>I need to be able to only show records that don't have any planningjob associated at all

SELECT orderno, planningjob
FROM binding
WHERE planningjob IS NULL
ORDER BY orerno

Author

Commented:
Hi,

I wish it was that simple. There are some orderno that are duplicated 4 or 5 times. 2 of those records could have a null value and the others would have a proper value. So using your code returns orderno that do in fact have a record associated, if that makes sense?

Ben
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Ben,

You need to get a count of records that contain non- null planningJobs.   If that count is zero for any given OrderNo, then all of the planningJobs for that order number are Null (meaning that ordernumber should be selected).

- Create a new query in design view and open the binding table.
- Select OrderNo
- Add the following expressio as a new column:
   JobNull: IIf(IsNull([PlanningJob]),0,1)
   This will select 0 when PlanningJob is Null, and 1 when it is not null.
   You need to sum this column and group by OrderNo to get a "count of non-nulls per order".
- Click the Summation sign on the toolbar of the query builder.
- Select "SUM" for the Total box for the JobNull column
- Select "Group By" for the total box in the OrderNo column
- Add the following criteria for JobNull:
    = 0
  This will select all orders that only have null planning jobs.

The resulting SQL will look something like this:

SELECT Orderno, Sum(IIf(IsNull([PlanningJob]),0,1)) AS JobNull
FROM binding
GROUP BY Orderno
HAVING Sum(IIf(IsNull([PlanningJob]),0,1))=0;

Author

Commented:
Hi,

I am actually using Query Anlayzer in SQL 2000. Sorry, perhaps I loaded this in the wrong area.

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That still brings through jobs that have planningjob associated.

There can be multiple records per orderno but only some have planningjob associated. It's a pickle. I only want to see the orderno that have no planningjob associated at all






Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.