CODEWITHSUNDEEP
phpmysql
Michael Nixon
Michael Nixon

Reputation: 23

Comparing MySQL rows from different tables in PHP

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!

Upvotes: 1

Views: 18

Answers (1)

Divyun
Divyun

Reputation: 96

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.

Upvotes: 1

Related Questions