[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Help, Fliter 0.00 and Null's

Posted on 2011-04-21
9
Medium Priority
?
267 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

0
Comment
Question by:NeoAshura
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35440975
Are there fields denoting the amounts in both tables? If so what are the column names?
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35441069
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
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35441118
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:NeoAshura
ID: 35441458
please could you talk me through this query?

on what each bit does?

thanks
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35441620
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

0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 2000 total points
ID: 35441632
Does it work ok?

This bit LEFT JOIN table1 ON table3.MMobile_number = table1.EMobile_number simply define a link between the two tables. Being a LEFT JOIN it means that the leftmost table in the statement (table1 in this case) is the primary table and all records in there are always shown. The next table (table3 in this case) will show results if there is a match and NULL for every field that lacks a match.

The tables are matched up by the ON clause ON table3.MMobile_number = table1.EMobile_number which tries to match up the data based on the column names, so in this instance it matches records from table3.MMobile_number to those with table1.EMobile_number where the two fields have equal values.


This gives us a set of data. The WHERE clause is now applied and all records which fail to match it are rejected. This leaves us with a set of records from table1 and, where possible, records from table3 that have the same mobile numbers but whose elast_months_bill fields are different.

The OR means it will not be an efficient query so make sure that the Mobile numbers and elast_months_bill columns in all these tables have indexes on them or else this will run so slowly you'll be posting a question about database performance.....  ;-)
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35441640
Ha! Just seen what you typed while I was typing. See my comment about indexes.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35441698
yup just got it cheers ill try work out how to put indexes on
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35441724
cheers worked thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month19 days, 5 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question