AMPscript: pulling from a data extension

Question

I have 2 data extensions:

1. advisers and account directors (lookup file). Table headers: ADVISOR, ACCOUNT_DIRECTOR, ADVISOR_EMAIL, ADVISOR_PHONE, ADVISOR_BRANCH.
2. directors emails (send data). Table headers: ACCOUNT_DIRECTOR, ACCOUNT_DIRECTOR_EMAIL. In the first one each account director may have one or more adviser listed. E.g. account director John has Joe, Marry, Priscilla and Tim as advisers (rows 1, 15, 23 and 24).

I need to email each account director from the second data extension (send data file), a table with this header (all her or his advisors): ADVISOR, ADVISOR_EMAIL, ADVISOR_PHONE, ADVISOR_BRANCH

Basically, I need to pull all advisors associated to every account director.

How can I do this with AMPscript?

Answer 1

Here you go. Should point you in the right direction:

<table>
    <tr> 
    <td>Advisor</td>
    <td>Advisor Email</td>
    <td>Advisor Phone</td>
    <td>Advisor Branch</td>
    </tr> 
%%[
/* pull the rowset */
set @rows = lookuprows("Advisors and account directors", "account_director", [Account_Director])

if rowcount(@rows) > 0 then 

    /* Iterate through each row and print results to html */
    for @i=1 to rowcount(@rows) do 
        set @row = Row(@rows, @i) 
        set @advisor = field(@row, "advisor")
        set @advisor_email = field(@row, "advisor_email")
        set @advisor_phone = field(@row, "advisor_phone")
        set @advisor_branch = field(@row, "advisor_branch") 

        ]%%
        <tr> 
        <td>%%=v(@advisor)=%%</td>
        <td>%%=v(@advisor_email)=%%</td>
        <td>%%=v(@advisor_phone)=%%</td>
        <td>%%=v(@advisor_branch)=%%</td>
        </tr> 

        %%[

    next @i

else
    /* default logic if there are no rows found for this account director */
endif 
]%%
</table>