Joining tables together in a model function

[eluser]jpidgeon[/eluser]
Thankyou for your replys

intractve - I tried your suggestion but think adding an if statement will only parse the where clause for the entire function, not on a row by row basis which is the case

Just to explain further my reasoning for splitting up the table structure.

Of the 3 tables - criteria, data and tasks - we don't need to worry about tasks as I can join that fine.

The criteria table sets out how my form is dynamically displayed for each criteria item (related to a task) There can be up to 50 criteria items per task.

The data is then related to each criteria item by "criteria_id" which then populates the form fields set out by the criteria table.

What it basically means is that for every task there will also be upto 50 criteria rows that should always be shown, but the data which is joined won't always exist until it's inserted or updated. I need both to be pulled together even if the relational data doesn't exist.

jaketoolson - I did think about doing it this way. I'm using a right join so that duplicate data from the criteria table overpowers that of the data table on the left.

I haven't ever tried it before, but maybe the UNION feature could be used here?

My only other option is to pre-populate the data table when a task is created, ensuring the relational data already exists, but this doesn't seem very efficent if only 4 of the 40 criteria rows have content.