The code I have works so far, but I’m a bit lost as to the next step.
sorry if this should be posted elsewhere - it’s not integrated into my modx yet
$query = "SELECT
events_ref.ev_name,
master_ev.ev_date,
animals.aname,
notes.note,
species.species,
IF( events_ref.ev_name='feeding', foodinv.food_type, IF( events_ref.ev_name='notes', notes.note, events_ref.ev_name ) ) AS eventOrFoodOrNote
FROM
master_ev
INNER JOIN animals ON (master_ev.animal_id=animals.animal_id)
INNER JOIN species ON (animals.species=species.species_idx)
INNER JOIN events_ref ON (master_ev.events_idx=events_ref.event_id),
feedings
INNER JOIN foodinv ON (feedings.perm_id=foodinv.perm_id),
notes";
foodinv.food_type and notes.note obviously pull in the first record on those tables, how can I pull a record that matches with events_idx?
I am at the very early stages as you can see, but once I am done I plan on sharing all the code
Thanks!
Got It!
SELECT
master_ev.ev_date,
animals.aname,
species.species,
species.subspecies,
IF(events_ref.ev_name = 'feeding', foodinv.food_type, IF(events_ref.ev_name = 'notes', notes.note, events_ref.ev_name)) AS event_fixed
FROM
master_ev
INNER JOIN animals ON (master_ev.animal_id=animals.animal_id)
INNER JOIN species ON (animals.species=species.species_idx)
INNER JOIN events_ref ON (master_ev.events_idx=events_ref.event_id)
LEFT OUTER JOIN notes ON (master_ev.ev_id=notes.ev_id)
LEFT OUTER JOIN feedings ON (master_ev.ev_id=feedings.ev_id)
LEFT OUTER JOIN foodinv ON (feedings.perm_id=foodinv.perm_id)
