> I wish to create a temporary view which have all the (distinct) values of > Fulladdress from all the tables where a permitno is equal to xx, but I need > to JOIN them so it will be Only one fulladdress column.
It sounds like you need to UNION the tables, not join them: SELECT PermitNo, FullAddress FROM Table1 UNION ALL + SELECT PermitNo, FullAddress FROM Table2 UNION ALL + SELECT PermitNo, FullAddress FROM Table3 UNION ALL + SELECT PermitNo, FullAddress FROM Table4 UNION ALL + SELECT PermitNo, FullAddress FROM Table5 UNION ALL Note that the UNION ALL will ensure that every table is represented for each PermitNo, including cases where the FullAddress field is exactly the same. To eliminate those duplicates, drop the ALL keywords. You might also want to include a third column with the table name spelled out, so you can find the records in the proper table. -- Larry
