How can I pull multiple rows from two data extension by lookup?

Question

I am using lookup to search records.

In Data extension 1, I have EmailAddress set as primary key In Data extension 2, I am allowing multiple rows of same email address

Data extension 1 data example: Email State [email protected] TX [email protected] TX

Data e xtension 2 data example: Email Group [email protected] A [email protected] B [email protected] C

So, in the search results by lookup, i want to display ALL rows that meets the search criteria.

In this example, if I lookup search for email "[email protected]"

The search result should be:

[email protected] A [email protected] C

This is the ampscript that I am using and currently the issue is that i am getting ONLY 1 row for the search term when there should be more than 1 row as the search term (email) exists more than once in dataextension2.

   %%[
var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i, @j, @rowTWO, @lookupValueTWO, @row2ndDE 

set @lookupValue = "[email protected]"
set @numRowsToReturn = 0
set @rows = Lookuprows("Dataextension1","Email", @lookupValue)
set @rowsTWO = Lookuprows("Dataextension1","Email", @lookupValue)
set @rowCount = rowcount(@rows)



if @rowCount > 0 then

for @i = 1 to @rowCount do

var @DEColumn1, @DEColumn2, @DEColumn3
set @row = row(@rows,@i) 
set @row2ndDE = row(@rowsTWO,@i) 
set @DEColumn1 = field(@row,"Email")
set @DEColumn2 = field(@row,"State")
set @DEColumn3 = field(@row2ndDE,"Group")

]%%

Email is %%=v(@DEColumn1)=%%, State is %%=v(@DEColumn2)=%%, Group is %%=v(@DEColumn3)=%%<br/>

%%[

next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%

Answer 1

You can't mix row sets like that. You'll need to include an additional LookupRows():

%%[
var @rows, @row, @rowCount, @i
var @rows2 @row2, @rowCount2, @j

set @email = AttributeValue("_subscriberkey")
set @rows = LookupRows("DataExtension1","Email", @email)

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @EmailAddress, @State, @Group
        set @row = row(@rows,@i)

        set @EmailAddress = field(@row,"Email")
        set @State = field(@row,"State")

        outputline(concat("<br>", @i , ". Email: ", @EmailAddress))
        outputline(concat("<br>", @i , ". State: ", @State))        

        set @rows2 = LookupRows("DataExtension2","Email", @email)
        set @rowCount2 = rowcount(@rows2)

        if @rowsCount2 > 0 then

          for @j = 1 to @rowCount2 do

            set @row2 = row(@rows2,@j) 
            set @Group = field(@row2,"Group")

            outputline(concat("<br>", @j , ". Group: ", @Group))

          next @j

        endif

    next @i 

else

    outputline(concat("<br>no rows found"))

endif 

]%%