Joining 3 tables

[Livvy Love]

$sql = "SELECT n.nid, n.title, n.type, n.changed, n.uid, v.timestamp, v.value, v.uid
FROM {node} n INNER JOIN {votingapi_vote} v on n.nid = v.content_id
WHERE v.uid = $uid
ORDER BY v.timestamp DESC"

So the SQL above tells me which nodes the user has voted on and what the name of the node is and also list them in the order that the person has voted. Now I want to combine another table that will give the information about how many total votes the node has. I have tried the code below but it doesn't work.

The votingapi_vote and the votingapi_cache need to be joined with the content_id tables that they both have. The votingapi_cache has the sum of the votes for the node. The below SQL is how I got the other tables.


$sql = "SELECT n.nid, n.created, v.content_id, v.value, v.tag, v.function
FROM {node} n INNER JOIN {votingapi_cache} v ON n.nid = v.content_id
WHERE n.status = 1 AND v.tag = 'vote' AND v.function = 'sum'
ORDER BY v.timestamp DESC"

I have tried this and recieved all sorts of errors

$sql = "SELECT n.nid, n.title, n.type, n.changed, n.uid, v.timestamp, v.value, v.uid
FROM {node} n INNER JOIN {votingapi_vote} v on n.nid = v.content_id
INNER JOIN {votingapi_cache} o on n.nid = o.content_id
WHERE v.uid = $uid AND o.function = 'sum'
ORDER BY v.timestamp DESC"


I'm still trying to learn MySQL and tried all the different joins but couldn't get those 3 tables together.

Ok so I spent pretty much all day trying to figure this out then after my customer training class I come back and write on the board. I tried to code that wouldn't work a few hours ago and guess what, it worked. How nice :P It made me look like a fool.



Edited 2 time(s). Last edit at 04/10/2006 01:39PM by Livvy Love.