SQL, recordsets and multiple table queries.

OK, first of all I suspect that you could better normalize the design by either

1) Create a new table with a single column to store the item with a flag to indicate whether it is wanted or offered. The table would link to the advert and have a row for each advert/item combination, or

2) Create two tables; items_wanted and items_offered

I think either way would simplify this query, and probably more that you haven't thought of yet.

Regardless, your current issue is that you have a table with a many to many relationship with itself. That's not a problem. You are using the offer table to link the advert to itself. The problem is that you want to link the table to itself on two fields. Still, it's do-able. The two options here are whether you want all details on a single row. For example, if a advert has items wanted and items offered, do you want to display both corresponding offer details on the same row? Or, create two lines for each advert/offer combo - one for the wanted items and one for the offered items.

For the first, you need 5 tables. The original advert table and offer table, and another two aliases for advert and one alias for offer. The SQL would be something like:

SELECT advert.username, advert.o_make, advert.o_model, advert.w_make, advert.w_model, advert_1.o_make, advert_1.o_model, advert_2.w_make, advert_2.w_model, advert_1.username
FROM (((advert LEFT JOIN offer AS offer_1 ON advert.advert_id = offer_1.to) LEFT JOIN advert AS advert_1 ON offer_1.from = advert_1.advert_id) LEFT JOIN offer ON advert.advert_id = offer.from) LEFT JOIN advert AS advert_2 ON offer.to = advert_2.advert_id;

For the second option, you could create a union to list the items offered on one line and the items wanted on another line. The syntax between the two UNION queries would be similar, having the advert table outer joined to the offer table which is then outer joined again to an alias of the advert table. The only difference is the joins on the 'from' and 'to' would be reversed.

>To be honest I did try to do it like this but

>couldn't then select the binded objects in

>dreamweaver as the syntax was incorrect.

OK, I'm not exactly sure what you tried, but one of your problems could be due to your use of Select *.  It's almost always better to explicitly state the list of columns you want. This is extemely important if you have two tables that share column names as they will be ambiguous. And if you are aliasing a second adverts table as I suggested, then you are guaranteed to have ambiguous columns so you need to list the columns explicitly.