Two column join question

[polisasimo]

Hello I am having a bit of trouble with a two column join query. The Customer table has 4 columns ID, NAME, STATE, PHONE_NUMBER. The Vendor table has 5 columns ID, NAME, STATE, PHONE_NUMBER, DISCOUNT.



I built a query with a two column join on NAME and STATE that returns Customer.PHONE_NUMBER and Vendor.DISCOUNT. Some rows are being omitted from the return data set because some Vendor.STATE values are NULL.

I need those returned in the query as well.

Any information would be great.

[pbaldy]

Have you tried editing the join to return all records from the customer table (LEFT or RIGHT join)?

[June7]

I don't understand joining Customer Name to Vendor Name.

BTW, Name is a reserved word. Should avoid reserved words as field names.

[polisasimo]

I used fake column names for this post. I just need to figure out how to do a two column join wherein some of the values in one table are NULL. When I use the access join property options to create what would be an inner - outer. I get:

The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.

[pbaldy]

Both joins would need to be the same I suspect. Easier if you post the SQL.

[polisasimo]

SELECT [TableA].A, [TableA].B, TableB.A, TableB.B
FROM [TableA] INNER JOIN TableB ON ([TableA].X= .TableB.X) AND ([TableA].[Y] = TableB.Y);

Some records in TableA will have a NULL value in TableA.Y so I just need those records returned based on the TableA.X and TableB.X values being equal.

Maybe I need a subquery?

[pbaldy]

Try changing INNER to RIGHT.

[polisasimo]

Changing INNER to RIGHT returns the TableB.A and TableB.B values but the records are missing the TableA.A and TableA.B data.

[polisasimo]

I think I figured it out thanks for pointing me in the right direction.

SELECT [TableA].A, [TableA].B, TableB.A, TableB.B
FROM [TableA] INNER JOIN TableB ON ([TableA].X= .TableB.X) AND (([TableA].[Y] = TableB.Y) OR (TableB.Y IS NULL));