mysql - possible to join on more than one field?

Question

I'm trying to write a join statement, but am finding that the query I'm running outputs the same information for each title found. It seems like I need to join tables on multiple fields, and I'm not sure that's possible? It's a little hard to explain, but the code below shows what I'm trying to accomplish and what's actually happening... Hopefully, someone will be able to point me in the right direction?

+----------------------------------------------------+
TABLE: NODE N
+----------------------------------------------------+
nid   | vid   | type  | language | title      | uid
22196 | 22196 | einfo | en       | Contact 15 |  15
22040 | 22040 | fmp   | en       | John Doe   |  15
22043 | 22043 | fmp   | en       | Jane Doe   |  15

+----------------------------------------------------+
TABLE: CONTENT_FIELD_PN P
+----------------------------------------------------+
vid   | nid   | delta | field_name_nid 
22196 | 22196 |     0 |          22040 
22196 | 22196 |     1 |          22043 


+----------------------------------------------------+
TABLE: CONTENT_FIELD_PP PP
+----------------------------------------------------+
vid   | nid   | delta | field_homephone_value
22196 | 22196 |     0 | 6505551212
22196 | 22196 |     1 | 6505551444

I'm trying to write a join statement that will produce:

+----------------------------------------------------+
TRYING TO OUTPUT
+----------------------------------------------------+
title       | field_homephone_value | delta
John Doe    | 6505551212            | 0
Jane Doe    | 6505551444            | 1

+----------------------------------------------------+

WHAT IS CURRENTLY BEING OUTPUT

+----------------------------------------------------+
title       | field_homephone_value | delta
John Doe    | 6505551212            | 0
Jane Doe    | 6505551212            | 1

+----------------------------------------------------+
THE QUERY AS IT'S CURRENTLY WRITTEN
+----------------------------------------------------+

SELECT p.field_name_nid, n2.title, p.delta, pp.field_homephone_value
FROM node n 
LEFT JOIN content_field_pn p ON n.nid = p.nid 
LEFT JOIN node n2 ON p.field_name_nid = n2.nid 
LEFT JOIN content_field_pp pp ON p.nid = pp.nid 
WHERE n.nid = 22196 
GROUP BY p.delta

Answer 1

You could use and in the on clause:

LEFT JOIN content_field_pp pp ON p.nid = pp.nid AND p.vid = pp.vid
                                                ^^^^^^^^^^^^^^^^^^

Answer 2

You may join on multiple fields...

SELECT `blah` FROM `a` LEFT JOIN `b` ON `b`.`id` = `a`.`id` AND `b`.`field2` = `a`.`field2`