Join two tables

[wpdfran]

I have a table which contains X and Y locations. I want to display all of them and next to each I would like to display the information from another table where it correlates.

This code gives me exactly what I need from the second table:

$result = mysql_query(“SELECT *,SUM(weight) FROM collection WHERE timestamp >= ‘2014-08-12 06:00:00’ AND timestamp <= ‘2014-08-13 05:59:00’ GROUP BY X, Y”);

X1.5 Y25.5 37
X1.5 Y25.6 247
X2 Y21 65

Where the first table contains every XY location and the second table only shows the totals of the XY locations that have been populated.

When I am done, it should look like this:

X Y Weight
X1 Y30
X1.5 Y25.5 37
X1.5 Y25.6 247
X2 Y15
X2 Y21 65

I think I need to JOIN the tables but I don’t know how to combine them to get what I need.

[scottlpool2003]

[PHP]
SELECT table1.id, table1.SUM(weight) AS weight, table2.id, table2.table1_id, table2.timstamp
FROM table1
INNER JOIN table2 ON table2.table1_id = table2.id
WHERE table2.timestamp >= ‘2014-08-12 06:00:00’ AND timestamp <= ‘2014-08-13 05:59:00’
GROUP BY X,Y
[/PHP]

If that makes sense…

Also, scrap mysql_query – severely outdated see here: http://php.net/manual/en/function.mysql-query.php

[wpdfran]

Thanks for your help. I am closer.

I put together this:

$sql =“SELECT collection.X, collection.Y, SUM(collection.weight) AS weight, locations.X, locations.Y, locations.production, locations.company, locations.shop, locations.commodity FROM locations RIGHT JOIN collection ON collection.X = locations.X AND collection.Y = locations.Y WHERE collection.timestamp >= ‘2014-08-12 06:00:00’ AND collection.timestamp <= ‘2014-08-13 05:59:00’ GROUP BY collection.X, collection.Y”;

Production Company Area Material X Y Sum
N company1 SQA Dead Parts 25.5 26.4 108
N company1 SQA Dead Parts 25.5 26.6 432
128.5
73.5
Y company2 Trim CB 29 2 273

I need it to show all the x y locations from the locations table even if there aren’t any listed in the collections table