SQL, recordsets and multiple table queries.
I have a table and a recordset containing the following SQL. which displays a members adverts if they have any offers or not. This works fine.
SELECT * FROM (
SELECT * FROM advert LEFT JOIN offer ON advert.advert_id = offer.to)
a WHERE a.user_id = (SELECT members.user_id from members where members.username = sessionuser)
Basically the offers table contains an offers.from and an offers.to foreign key to the adverts parent table.
I can display the offers.to foreign key and link it to the advert table fine.
Now the problem I have is I need to link the offer.from foreign key to the advert table.
I created a 2nd recordset using a URL parameter offers.from. to access the adverts table again for the information (on a different record)
Although this works using the 'test' feature in the SQL dialogue box (in which the offer.from parameter is entered), when I run it nothing is displayed. I have even displayed offer.from in the table to make sure it is getting that value, which it is.
Is this because..........
I have chosen to display only records for the the users particular login in the 1st recordset.
recordset1 and recordset2 are not linked?
Any other suggestions.
