Hi guys,
I cannot finish my SQL query with relationship fields. This is my code:
{exp:query sql="
SELECT DISTINCT exp_channel_data.field_id_97 AS title, exp_channel_data.field_id_98 AS id, exp_channel_data.field_id_399 AS ratio, exp_channel_data.field_id_160 AS fullscreen_mode,
REPLACE (exp_channel_data.field_id_454, '\\\\{filedir_29\\\\}', '') AS image
FROM exp_channel_titles
JOIN exp_channel_data ON exp_channel_data.entry_id = exp_channel_titles.entry_id
JOIN exp_category_posts ON exp_category_posts.entry_id = exp_channel_titles.entry_id
JOIN exp_relationships ON exp_relationships.child_id= exp_channel_titles.entry_id
WHERE exp_channel_titles.channel_id='34' AND exp_relationships.field_id = 455 AND(expiration_date = '0' OR expiration_date > UNIX_TIMESTAMP(NOW())) AND exp_category_posts.cat_id = '{embed:cat}' AND status = 'open'
ORDER BY exp_channel_titles.entry_date
"}
<a href="#" class="class2">/images/uploads/example/{image}<div class="winner"><span class="title">{title}</span><span class="js3"></span></div></a>
</div>
{/exp:query}
I want to show some entries (status = open, embed category, channel id) but base on relationship field.
Code above doesn’t work. When I replace the line with WHERE using this: “WHERE exp_relationships.field_id = 455” then finally I got all entries related to the field 455 but I want to also filter them by status, category and channel.
How can I do it if I have parent filed in one channel and 4-5 fields (children) in another channel?
Thanks for your help!
Packet Tide owns and develops ExpressionEngine. © Packet Tide, All Rights Reserved.