Hello,
I'm a newbie with MySQL and would like to get some help with a JOIN selection query.
Let's see the example-code:
Table 1: as T1
type lot bought
A 10022 30
A 10023 30
B 10024 30
B 10025 30
Table 2: as T2
type lot used
A 10022 4
A 10022 8
B 10024 6
B 10024 2
What I am doing is retrieving elements from the DB with a fetch
// SQL Query
$sql = mysql_query("SELECT * FROM T2"
while ($row = mysql_fetch_array($sql)) {
$req = $row["req"];
$name = $row["guest_name"];
$email = $row["guest_email"];
$array = $row["array"];
$chips = $row["chips"];
$target = $row["target"];
$lot = $row["lot"];
}
What I' d like to get to is this:
Result Table
type lot bought available
A 10022 30 26
A 10023 30 30
B 10024 30 22
B 10025 30 30
So I should define inT2 USED as a variable, calculate a USED_SUM restricted to lot number and (under a JOIN selection) subtract this value to the value of BOUGHT from T1 actually 30.(Everything inside a loop query that gets rid of duplicate entries from T2).
Too hard for me!!
Any help would be very appreciated!!
Thanks in advance,
Simone
I'm a newbie with MySQL and would like to get some help with a JOIN selection query.
Let's see the example-code:
Table 1: as T1
type lot bought
A 10022 30
A 10023 30
B 10024 30
B 10025 30
Table 2: as T2
type lot used
A 10022 4
A 10022 8
B 10024 6
B 10024 2
What I am doing is retrieving elements from the DB with a fetch
// SQL Query
$sql = mysql_query("SELECT * FROM T2"
while ($row = mysql_fetch_array($sql)) {
$req = $row["req"];
$name = $row["guest_name"];
$email = $row["guest_email"];
$array = $row["array"];
$chips = $row["chips"];
$target = $row["target"];
$lot = $row["lot"];
}
What I' d like to get to is this:
Result Table
type lot bought available
A 10022 30 26
A 10023 30 30
B 10024 30 22
B 10025 30 30
So I should define inT2 USED as a variable, calculate a USED_SUM restricted to lot number and (under a JOIN selection) subtract this value to the value of BOUGHT from T1 actually 30.(Everything inside a loop query that gets rid of duplicate entries from T2).
Too hard for me!!
Any help would be very appreciated!!
Thanks in advance,
Simone