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"