I'm not an SQL guru, so take this with a grain of salt: You could do a join on table1.firstname=table2.firstname and table1.firstname = table2.lastname to get all the results that match.
You could union this to two unioned selects where there's an entry in table1 but not table2 and vice versa. You'll have to select empty strings for the columns that are only present in one or the other table. This should give you a result set where you either have the addresses from one, or the other, or both. You can make display (or further processing) decisions based on that. --Ben Doom Mark Henderson wrote: > OK, so that was working, and those are the only two fields I want to > union on, which is fine. But now I also want to add the address fields > to the query result, without doing a union on those additional fields. > Some addresses aren't entered quite right, and this will then create > duplicates even though the query doesn't see them as such - does that > make sense? > > So, for instance, when I do: > > SELECT FirstName1 AS FirstName, Surname1 AS LastName > FROM tblMain > WHERE foundation = Yes > UNION > SELECT FirstName, LastName > FROM tblCentennialMembers; > > I get the result set I want. But if I do > > SELECT FirstName1 AS FirstName, Surname1 AS LastName. Add1, Add2, City, > Country > FROM tblMain > WHERE foundation = Yes > UNION > SELECT FirstName, LastName, Address1, Address2, City, Country > FROM tblCentennialMembers; > > It returns almost everything including the duplicates since add1 doesn't > always match address1, even though the first and last names match. > > So, how to get the extra fields into the original result set while only > doing a union on first and last names? > > > Mark > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300083 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
