Comparing MySQL rows from different tables in PHP

Question

I'm building an application that tells a user whether the resources they have are sufficient for an activity. Here's an example of the sort of table that I am using to store what resources are needed (note: the IDs are only what are required for that activity, so can sometimes have gaps in the actual application):

Table 1 (resources needed)
--------------------------
ID     No. Required
--------------------------
1      12
2      3
3      6

I also have a similar table which stores the resources that the user already has, in which resources share the same IDs as in the first table.

Table 2 (resources available)
-----------------------------
ID     No. Available
-----------------------------
1      3
2      8
3      7

I already have an 2 SQL queries which bring in the resource IDs and quantities from both tables. Now I need to know how to compare the individual resources and return whether or not there are enough, and also return how many resources are needed if there are not enough.

Thanks in advance!

Divyun · Accepted Answer · 2017-04-16 00:50:15Z

1

Following SQL

select table1.id as "ID", table1.required-table2.available as "resources needed"
from table1 join table2 
on a.id=b.id

would result in this

-----------------------------
ID     resources needed
-----------------------------
1      9
2      -5
3      -1

A positive integer here means more resources are needed.

answered Apr 16, 2017 at 0:50

Divyun

964 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Nixon Over a year ago

Will this still work if there is not an ID for each resource in each table e.g. if the resource needed is not listed?

Divyun Over a year ago

The default join, ie. inner join, will ignore the values if the id is not available in both tables. You can take a look at other types of join here http://www.sql-join.com/sql-join-types/ and use the one which helps your cause.

Collectives™ on Stack Overflow

Comparing MySQL rows from different tables in PHP

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related