Join and Sum confusion...

Capoeirista

Hi There -

Thanks for giving this a look. This is spinning my brain in a bunch of different directions. I have two tables. Their common column is pid.

table_1
item | pid | count
soap | 001 | 4
shoe | 002 | 2
cups | 003 | 3
soap | 004 | 2

where count is the number of items associated with a pid (purchase id)

table_2
pid | uid | type
002 | 1 | amex
003 | 2 | visa
004 | 1 | amex
001 | 3 | visa

where uid is the user id and type is the type of purchase.

What I'm trying to do is get a total of the number of items a user has purchased of a certain type.

In the above example, I need to figure out how to join the two tables and extract the total count of soap that user 1 has purchased using amex, which would be 6.

Here's what I've worked out. MySQL just laughs at me.

SELECT uid, count WITH ROLLUP
FROM table_1 JOIN table_2 ON (pid = pid)
WHERE uid = $uid and type=amex;

'WITH ROLLUP' is supposed to create a new row with a total on it. Honestly. I have no idea how to proceed from here. I'm really not grokking how to extract the rollup number from that join.

Same with this:
SELECT uid, count SUM(amount) AS total_amount
FROM table_1 JOIN table_2 ON (pid = pid)
WHERE uid = $uid and type=amex;

On a really basic level, I don't really get the extraction of data from a join or a computation. Put them both together and I'm really lost.

I'd be grateful for any nudges in the right direction or clear explanations of how to get stuff out of a join. I also don't really understand how to use 'AS' and it's very difficult to search for.

Thanks!

Kudose

SELECT t1.uid, SUM(t1.amount) AS total_amount FROM table_1 AS t1, table_2 AS t2 WHERE t1.pid=t2.pid AND t2.type='amex'

That should work, but I haven't tested it.

"AS" basically lets you reference an element by a different name.

I suppose you could write it like this too, but I prefer shorter references to table names.

SELECT table_1.uid, SUM(table_1.amount) AS total_amount FROM table_1, table_2 WHERE table_1.pid=table_2.pid AND table_2.type='amex'

You would then be able to access the elements like so:

$sql = "SELECT t1.uid, SUM(t1.amount) AS total_amount FROM table_1 AS t1, table_2 AS t2 WHERE t1.pid=t2.pid AND t2.type='amex'";
$query = mysql_query($sql);
$data = mysql_fetch_array($query);
echo 'Total Amount: '.$data['total_amount'];
echo '<br />UID: '.$data['uid'];

Hope that helps.

Capoeirista

Yep. I'm amazed and grateful and stunned.

You have managed to take something that I've read so many tutorials on that my head is spinning and in a few short sentences and a couple of examples, made it beautifully clear. Rare gift. I hope they appreciate you wherever you're working.

So if t1 has the pid, the item and the count,
and t2 has the pid, the uid and the type,
and I'm given the uid and the item and the type and want to find the total counts for that item given the uid

would it be like this?

SELECT SUM (t1.count) AS total_amount from t1 WHERE t1.pid=t2.pid AND t2.uid = $uid AND t2.type='amex' AND t1.item='soap'

Since this approach seems much more clear, why do people do joins? Are they less processor intensive?

laserlight

Since this approach seems much more clear, why do people do joins? Are they less processor intensive?

Look carefully: Kudose's example performs a join ("FROM table_1 AS t1, table_2 AS t2").

Capoeirista

That's cool. You don't have to explicitly say JOIN? When it's worded that way, is it a 'straight' join? (as opposed to inner, left, right etc...)

Fascinating. Thanks for letting me know. I appreciate it.

laserlight

That's cool. You don't have to explicitly say JOIN? When it's worded that way, is it a 'straight' join? (as opposed to inner, left, right etc...)

I do not know what is a 'straight' join, but this would be an inner join. Of course, if you did not specify a WHERE clause it would be the cartesian product/join.