I try to write a join statement, but I find that the query being executed displays the same information for every header found. It looks like I need to join tables in multiple fields, and I'm not sure if this is possible? It's a little hard to explain, but the code below shows what I'm trying to execute and what is actually happening ... I hope someone can 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 am trying to write a union operator that will produce:
+----------------------------------------------------+
TRYING TO OUTPUT
+----------------------------------------------------+
title | field_homephone_value | delta
John Doe | 6505551212 | 0
Jane Doe | 6505551444 | 1
+----------------------------------------------------+
WHAT YOU NEED TO EXIT
+
title | field_homephone_value | delta
John Doe | 6505551212 | 0
Jane Doe | 6505551212 | 1
+
THE QUERY AS IT 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
source
share