We help IT Professionals succeed at work.

PHP Mysql HELP PLEASE

NeoAshura
NeoAshura asked
on
Medium Priority
304 Views
Last Modified: 2012-08-13
Hi Experts,

I have a database, Inside this database i have two tables

One table is the data we send off to find out if we have been paid for upgrades we have done on an account.

And the other table is the data which gets sent back to show which upgrades we have been paid for.

There WILL be some numbers missing from the upgrades WE HAVE been paid for compared to the numbers we SHOULD HAVE been paid for.

I somehow need to Flag up and compare the NUMBERS from Table 1 to Table2

So if NUMBERS in table 1 dont appear in table 2 THEN it must be filtered through to the page and displayed.

If you require a database layout of tables etc to help me with this then that can be provided.



Many thanks for your time
Comment
Watch Question

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

Author

Commented:
thanks its not the amounts, that i need to look at its mobile numbers, If they exsist in one table but are not in the other table then we have not been paid for them.

ill give them a whirl ill set up my databases now and get back to you.

Author

Commented:
Ok i have the following tables please see screenshots.

As you can see all table layouts are the same but contain different information.

Table 3 is going to be the KEY TABLE because it contains the contains 100% correct information.

table 2 and  table 1 is what is sent to us from suppliers which could be incorrect.

Need to flag up numbers that appear in table 3 that DO NOT appear in tables 1 or tables 2.

Is this possible??

Hopefully my screenshots will help a little better??

As you can see from table 3 image there are 2 numbers that dont exsist in table 1 and one number that does not exsist in table2

I need to flag which numbers dont exist in the tables 1 and 2.

please help
table1.png
table3.png
table2.png
CERTIFIED EXPERT

Commented:

SELECT t3.Mobile_number FROM table3 t3
LEFT JOIN table2 t2 on t3.key = t2.key
WHERE  t2.Mobile_number IS NULL

and then run this for the other table

SELECT t3.Mobile_number FROM table3 t3
LEFT JOIN table1 t1 on t3.key = t1.key
WHERE  t1.Mobile_number IS NULL

CERTIFIED EXPERT

Commented:
Argggghhh!! Missed a bit.... why do I always see it AFTER pressing submit???

SELECT t3.Mobile_number FROM table3 t3
LEFT JOIN table2 t2 on t3.Mobile_number= t2.Mobile_number
WHERE  t2.Mobile_number IS NULL

and then run this for the other table

SELECT t3.Mobile_number FROM table3 t3
LEFT JOIN table1 t1 on t3.Mobile_number= t1.Mobile_number
WHERE  t1.Mobile_number IS NULL

Author

Commented:
Ok ive solved it used the following query

SELECT * FROM table3
LEFT JOIN table1 on table3.Mobile_number = table1.Mobile_number
WHERE  table1.Mobile_number IS NULL OR table3.Mobile_number<> table1.Mobile_number

As suggested.

and worked fine.

many thanks
CERTIFIED EXPERT

Commented:
Given you are using phone numbers I would try dropping the OR table3.Mobile_number<> table1.Mobile_number as the JOIN prohibits it

SELECT * FROM table3
LEFT JOIN table1 on table3.Mobile_number = table1.Mobile_number
WHERE  table1.Mobile_number IS NULL

If it doesn't work then change it back
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.