Conditional joins

[ltcarlisle]

Hi all,

I have two tables. Both have first names and last names in each record and each record in each table has a unique ID. Table A has a city and business name for each record, but in Table B only some entries have cities and business names, some only have cities, some only have business names and some don't have either. I'd like to set up a conditional join between tables A and B, one that follows this logic:

If table B's record (denoted by first/last name combo) has BOTH a city and business name, join to table A's record on first name/last name/city/business name.



If table B's record has ONLY a city, join to table A's record on first name/last name/city.

If table B's record has ONLY a business name, join to table A's record on first name/last name/business name.

--------------------------------------------------------------------------------------------------------------------

Is this possible?

[June7]

The unique ID is not a linking value?

Joins cannot be different for each record. The join is established between fields of tables/queries. Either field is included or it isn't. Can't be dynamic dependent on record.

[hapm]

Join by first and last name and filter the additional unwanted results with where clause
or
Use multiple queries and union the results

[ltcarlisle]

I could do this, but when I join only by first and last name there are incorrect matches between people with the same first and last name.

[June7]

And therein lies the conundrum caused by bad data.

Looks like will need to UNION 3 SELECT queries with appropriate INNER JOIN.

Can be done in one SQL statement built into the SQL View of query builder.

[hapm]

As I said you can filter these matches using the where clause.

[ltcarlisle]

I'm having trouble understanding how either of these strategies could work. Could you provide some example code?

[June7]

Build 3 queries with the joins you describe in first post. Use INNER JOIN and resulting dataset will show only records that have exact matches on each of the joined fields.

Then if you want to view the 3 queries as one combined dataset, build a UNION query. There is no wizard or designer for UNION, must type into SQL View of query builder.

[hapm]

SELECT A.*, B.* FROM A INNER JOIN B ON A.FirstName = B.FirstName AND A.LastName = B.LastName WHERE (A.City IS NULL OR A.City = B.City) AND (A.BusinessName IS NULL OR A.BusinessName = B.BusinessName)

[ltcarlisle]

Thank you both for the help