1

Pseudo Query:

Select zoo visitor channel fields from exp_channel data
And author_id from exp_channel_titles (joined on entry_id)
Where channel_id = my zoo visitor channel
And author_id does not have an entry where channel_id = orders channel

In english - I want to select some user data for all users who do not have an order. Can this be done entirely in a query, or would PHP be necessary? This isn't strictly an EECMS question, but I'll bet someone here can help. If you can frame out what that query would look like, bonus points!

Here's the query I have now, which is the first 3 lines, but doesn't account for 'no orders':

select cd.field_id_XX as first_name, cd.field_id_XX as middle_name,
cd.field_id_XX as last_name, cd.field_id_XX as suffix, 
ct.author_id as member_id from exp_channel_data as cd 
join exp_channel_titles as ct on cd.entry_id = ct.entry_id 
where cd.channel_id = "XX"
4

I think you would need a subquery to make this happen

select cd.field_id_XX as first_name, cd.field_id_XX as middle_name,
cd.field_id_XX as last_name, cd.field_id_XX as suffix, 
ct.author_id as member_id
from exp_channel_data as `cd`
left join exp_channel_titles as `ct` on cd.entry_id = ct.entry_id
where cd.channel_id = "XX"
and (select count(*) FROM exp_channel_titles where author_id = ct.author_id and channel_id = 'XXX') = 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.