I have a similiar extensible property|value table too and you just keep
joining the tables (user left join if potentially no record)
SELECT
,fname.COREFIELDVALUE as firstname
,lname.COREFIELDVALUE as lastname
,dob.COREFIELDVALUE as DOB
FROM
tblApplication a
INNER JOIN tblApplicantCoreDetails fname
ON fname.COREFIELDID = 2
and fname.applicantid = st.applicantid
INNER JOIN tblApplicantCoreDetails lname
ON lname.COREFIELDID = 5
and lname.applicantid = st.applicantid
INNER JOIN tblApplicantCoreDetails dob
ON dob.COREFIELDID = 6
and dob.applicantid = st.applicantid
make up a view as sugested. If it is *heavily* used in querying investigate
a trigger to populate a flat table used only for querying - this is what i
have done
Elliot
"Ricardo Russon" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> excuse me while i think out in open space, But if any one has any ideas,
> please throw them back at me.
>
> The table is structured in a fairly complex way. and it has to be this
way.
> It relates to 2 other tables for different reasons.
>
> So the complex table that i need to simplify on output is something like..
>
> id clientID fieldValue value
> 1 1 1 firstName1
> 2 1 2 middleName1
> 3 1 3 lastName1
> 4 2 1 firstName2
> 5 2 2 middleName2
> 6 2 3 lastName2
> 7 3 1 firstName3
>
> and so on...
> there are other values stored in that table, but i don't need them for
this.
>
> What i want to get out of it is something like.
>
> clientID 1 2 3
> 1 firstName1 middleName1 lastName1
> 2 firstName2 middleName2 lastName2
> 3 firstName3 middleName3 lastName3
>
> I guess what i am really trying to figure out is if i can do this against
> the database, or if i have to recreate the table on the cf.
>
> There is another step which i left out, that the column names (1,2,3...)
> actually get their values from another table. But that opens up so many
> more problems...
>
> baby steps.
>
> TIA
> Ricardo.
>
>
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
