Help with custom query using Query Module

The relationship field doesn’t actually store the weblog entry id of the related entry. It actually stores the relationship id from the exp_relationships table. I think in your case, you will want to do a join on the relationships table and a where on the rel_child_id = {segment_3}

Thanks for your reply, Tom. I took a look at my EE database again, and I get how the exp_relationships table works in now, but now it seems to me I need to join three tables: exp_channel_data & exp_channel_titles, and exp_channel_data & exp_relationships–and I’m not sure how to go about that. I came up with the following, but it’s extremely convoluted and doesn’t work:

{exp:query sql="

SELECT
t.title,
t.url_title AS permalink,
t.entry_id AS entry_id,
t.author_id AS author_id,
d.field_id_1 AS layout

FROM 
exp_channel_data AS d, 
exp_channel_titles AS t,
exp_relationships AS r

WHERE t.channel_id = '2' 

AND t.entry_id = d.entry_id,

AND d.field_id_21 = r.rel_id,

AND r.rel_child_id = '{segment_3}';"

}

I’m pretty new to SQL queries, so any pointers you can give me would be MUCH appreciated. What am I doing wrong? Is there a better way to join this data?

I would look to actually do an SQL JOIN in this case.

(simple example) SELECT your_fields FROM exp_channel_data JOIN exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id WHERE field = something

These links may be of help for you http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html http://dev.mysql.com/doc/refman/5.1/en/join.html

That example makes perfect sense to me, but it’s only joining two tables. Where I get hung up is when I need to join a third table, because I need to pull data from the exp_channel_titles AND exp_channel_data tables based on a value in the exp_relationships table. Can I do multiple joins in one query?

Something like this:

SELECT exp_channel_data.my_fields, exp_channel_titles.my_fields
FROM exp_channel_data, exp_channel_titles
JOIN exp_channel_titles ON exp_channel_data.entry_id = exp_channel_titles.entry_id
AND exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id
WHERE field = something

In the case of a JOIN instead of using an AND like you would in a WHERE clause you use another JOIN statement.

SELECT exp_channel_data.my_fields, exp_channel_titles.my_fields
FROM exp_channel_data, exp_channel_titles
JOIN exp_channel_titles ON exp_channel_data.entry_id = exp_channel_titles.entry_id
AND exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id
WHERE field = something

would be

SELECT exp_channel_data.my_fields, exp_channel_titles.my_fields
FROM exp_channel_data, exp_channel_titles
JOIN exp_channel_titles ON exp_channel_data.entry_id = exp_channel_titles.entry_id
JOIN exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id
WHERE field = something

Thanks, Tom! That makes a lot more sense. I retried my query as follows, using two JOINs:

SELECT t.title, t.url_title AS permalink, t.entry_id AS entry_id, t.author_id AS author_id, d.field_id_1 AS layout

FROM exp_channel_data AS d, exp_channel_titles AS t

JOIN exp_channel_titles ON exp_channel_data.entry_id = exp_channel_titles.entry_id JOIN exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_parent_id

WHERE t.channel_id = ‘2’ AND exp_relationships.rel_child_id = ‘{segment_3}’

But now I get a 1054 error that says “Unknown column ‘exp_channel_data.entry_id’ in ‘on clause’”, and I’m wondering if it’s because I’m calling the same column (exp_channel_data.entry_id) in two different JOINs?