Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

PHP Mysql HELP PLEASE

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
0
NeoAshura
Asked:
NeoAshura
  • 4
  • 3
2 Solutions
 
Greg AlexanderLead DeveloperCommented:
You can use a right join statement to get the results that do not have a match by this method

SELECT * FROM a_table1 A RIGHT JOIN a_table2 B on A.number = B.number WHERE A.id IS NULL

if you had two simple tables like this
CREATE TABLE IF NOT EXISTS `a_table1` (
  `id` int(11) NOT NULL auto_increment,
  `number` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
)

Open in new window


CREATE TABLE IF NOT EXISTS `a_table2` (
  `id` int(11) NOT NULL auto_increment,
  `number` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
)

Open in new window


0
 
Beverley PortlockCommented:
Similar to what galexander has done, but also looking for mismatched amounts

UNTESTED CODE

SELECT * FROM table1
LEFT JOIN table2 on table1.key = table2.key
WHERE  table2.someAmount IS NULL OR table1.someAmount <> table2.someAmount

0
 
NeoAshuraAuthor 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.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
NeoAshuraAuthor 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
0
 
Beverley PortlockCommented:

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

0
 
Beverley PortlockCommented:
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
0
 
NeoAshuraAuthor 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
0
 
Beverley PortlockCommented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now