i have a 2 table Table A and Table B i want to combine table in to one table.. i want the qurrey of the table.. i tried this querry..
SELECT COUNT(tablea.col_date)as totaljobs ,trans.collect_date,
ROUND(SUM(tablea.cloth_weight),2)as cloth_weight,
ROUND(SUM(tablea.cloth_amount),2) as clothes_amount,
ROUND(SUM(tablea.shoes_weight),2)as cloth_weight,
ROUND(SUM(tablea.shoes_amount),2) as clothes_amount,
tablea.col_date,tablea.driver,tableb.fuel,tableb.expense FROM tablea INNER JOIN tableb ON tablea.driver=tableb.driver WHERE tablea.col_date BETWEEN '2022-09-20' AND date(now()) GROUP BY col_date,tableb.driver;
Table A:
| id | col_date | clothe_kg | clothe_amount | shoes_kg | shoes_amount | driver |
|---|---|---|---|---|---|---|
| 1 | 2022-09-20 | 50 | 25 | 30 | 15 | john |
| 2 | 2022-09-20 | 50 | 25 | 30 | 15 | john |
| 3 | 2022-09-20 | 30 | 15 | 40 | 20 | john |
| 4 | 2022-09-20 | 40 | 20 | 40 | 20 | khan |
| 5 | 2022-09-20 | 40 | 20 | 20 | 10 | khan |
| 6 | 2022-09-20 | 50 | 25 | 20 | 10 | khan |
| 7 | 2022-09-21 | 50 | 25 | 20 | 10 | john |
| 8 | 2022-09-21 | 30 | 15 | 40 | 20 | john |
| 9 | 2022-09-21 | 60 | 30 | 40 | 20 | john |
| 10 | 2022-09-21 | 40 | 20 | 40 | 20 | khan |
| 11 | 2022-09-21 | 30 | 15 | 40 | 20 | khan |
| 12 | 2022-09-21 | 20 | 10 | 50 | 25 | khan |
| 13 | 2022-09-22 | 50 | 25 | 50 | 25 | khan |
| 14 | 2022-09-22 | 60 | 30 | 50 | 25 | khan |
| 15 | 2022-09-22 | 70 | 35 | 50 | 25 | john |
| 16 | 2022-09-22 | 80 | 40 | 30 | 15 | john |
Table B:
| id | driver | col_date | fuel | Van_number |
|---|---|---|---|---|
| 1 | john | 2022-09-20 | 30 | 3312 |
| 2 | khan | 2022-09-20 | 30 | 3314 |
| 3 | john | 2022-09-21 | 0 | 3312 |
| 4 | khan | 2022-09-21 | 0 | 3314 |
| 5 | john | 2022-09-22 | 0 | 3312 |
| 6 | khan | 2022-09-22 | 0 | 3314 |
Output:
| Sr | driver | col_date | fuel | Van_number | sum(clothesweight) | sum(clothesamount) | sum(shoeesweight) | sum(shoesamount) |
|---|---|---|---|---|---|---|---|---|
| 1 | john | 2022-09-20 | 30 | 3312 | 130 | 65 | 100 | 50 |
| 2 | khan | 2022-09-20 | 30 | 3314 | 130 | 65 | 80 | 40 |
| 3 | john | 2022-09-21 | 0 | 3312 | 130 | 70 | 60 | 30 |
| 4 | khan | 2022-09-21 | 0 | 3314 | 90 | 45 | 90 | 45 |
| 5 | john | 2022-09-22 | 0 | 3312 | 110 | 55 | 100 | 50 |
| 6 | khan | 2022-09-22 | 0 | 3314 | 150 | 75 | 80 | 40 |
CodePudding user response：
You were on the right track with the code you tried, good job for adding that. You need to include all non-aggregated columns in the GROUP BY and make sure you use all key columns in your JOIN. In this case you only joined on driver, but you need to join on driver AND col_date. And there was a trans.collect_date in your example code, not sure where that comes from.
I think this will help you out:
SELECT
tablea.col_date,
tablea.driver,
tableb.fuel,
tableb.Van_number,
ROUND(SUM(tablea.clothe_kg),2) as cloth_weight,
ROUND(SUM(tablea.clothe_amount),2) as clothes_amount,
ROUND(SUM(tablea.shoes_kg),2) as cloth_weight,
ROUND(SUM(tablea.shoes_amount),2) as clothes_amount
FROM tablea
INNER JOIN tableb
ON tablea.driver = tableb.driver
AND tablea.col_date = tableb.col_date
WHERE tablea.col_date BETWEEN '2022-09-20' AND date(now())
GROUP BY
tablea.col_date,
tablea.driver,
tableb.fuel,
tableb.Van_number;
CodePudding user response：
its very easy to fetch data from two table from database and display in single frontend table i hope you make connection to database.
fetch data from tables
$query1 = 'select * from A';
$query2 = 'select * from B';
$result1 = mysqli_query($con , $query1);
$result2 = mysqli_query($con , $query2);
if ($result1->num_rows > 0 && $result2->num_rows > 0 ) {
while($Adata = $result1->fetch_assoc() && $Bdata = $result2->fetch_assoc()) {
echo $Adata['columname'];
echo $Bdata['columname'];
}
else {
echo "0 results";
}
}