We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Query Help, Fliter 0.00 and Null's

NeoAshura
NeoAshura asked
on
Medium Priority
279 Views
Last Modified: 2012-05-11
I have 2 tables.

I have table 1 which is data that comes in and table 3 which is data i have supplied already which is correct.

I need to compare the data to see if the same numbers exist in table 1 as the number in table 3.

BUT ALSO

if they do exist but have 0.00 or are empty( NULL)

Then must also be flagged up EVEN IF they are in both tables.

the query i have so far which display which number ARE IN table 3 but not table 1 is (ATTACHED)

I need to modify this query to be able to also fliter and push out mobile_numbers where the last_months_bill is 0.00 or NULL

Many thanks for your time.

SELECT * 
FROM table3
LEFT JOIN table1 ON table3.MMobile_number = table1.EMobile_number
WHERE table1.EMobile_number IS NULL 
OR table3.MMobile_number <> table1.EMobile_number";

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
Are there fields denoting the amounts in both tables? If so what are the column names?

Author

Commented:
ill screenshot it for you.

Ive used this table to test my code etc.

as you can see i have one "NULL" and one 0.00

bost must be able to be filtered and stand out like using your last query.

even if they both appear in the same tables.
table2.png
CERTIFIED EXPERT

Commented:
Try this

SELECT *
FROM table3
LEFT JOIN table1 ON table3.MMobile_number = table1.EMobile_number
WHERE table1.EMobile_number IS NULL
OR table3.elast_months_bill <> table1.elast_months_bill

Author

Commented:
please could you talk me through this query?

on what each bit does?

thanks

Author

Commented:
im also getting.

Fatal error: Maximum execution time of 30 seconds exceeded in G:\EasyPHP-5.3.5.0\www\mobilepaid.php on line 100

when trying to run this query

any ideas why? im trying there are over 3,000 entries that need to be checked?

p.s line 100 is $result = mysql_query($query) or die(mysql_error() . " IN $query");

$query = "SELECT * 
FROM marksdata
LEFT JOIN o2data ON marksdata.MMobile_number = o2data.OMobile_number
WHERE o2data.OMobile_number IS NULL 
OR marksdata.MMobile_number <> o2data.OMobile_number";

Open in new window

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
Ha! Just seen what you typed while I was typing. See my comment about indexes.

Author

Commented:
yup just got it cheers ill try work out how to put indexes on

Author

Commented:
cheers worked thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*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.