Join Properties; DISTINCT to DISTINCT possible?

[jezjez]

Hi,

In the standard Join Properties box you have three option:

1 Only rows where joined fields both tables are equal
2 All records from table a and only those from table b where joined fields are equal
and 3 the oppiosite of 2

I have two tables matched on user where the user might have records in table a and none in table b and another user that might have records in table b and none in table a.

But i need them both to be output as the query result. I can not get the query to return all users even if they do not have matching records on a or b.

help appreciated...
thanks
db

 

[Pat Hartman]

You need a full outer join which Jet does not directly support. The simple answer is to create a left join query and a right join query and then include those queries in a union query. The Union will get rid of the duplicate rows (unless you specify Union All).

 

[jezjez]

Thanks Pat;

but can i specify a Union All - isn't that exactly my problem...? Where is this 'Union' setting?

cheers, Duncan

 

[Pat Hartman]

Unions are SQL specific so they cannot be built with the query builder. I would build the left join query and the right join query and save them. Then open the querybuilder to SQL view.

Select * From qryLeftJoin
Union Select * From qryRightJoin;

Make sure that both queries have the same number of columns in the same order.

but can i specify a Union All - isn't that exactly my problem

- No. The lefjoin query and rightjoin query will both return rows where the rows have a match in both tables and it is these duplicates you want to get rid of. You want to end up with a recordset like:

left - Right
Null - Right
Left - Null

 

[jezjez]

Thanks Pat; i also found UNION in help and had a first crack at the UNION query; i got it to work but it just appended one set of data to the bottom of the next..
So next i'll look at the grouping by and/or removal of duplicates bit...

what i have is:

query 1
user recorda
fred 5
colin 2
tom 0
paul 3

query 2
user record b
fred 3
paul 0
harold 2
tom 4

and what result i need is:

user reca recb
fred 5 3
colin 2 0
tom 0 4
paul 3 0
harold 0 2

The whole getting null to 0 is driving me crazy too, but thats perhaps a seperate issue...

cheers
Duncan

 

[Pat Hartman]

got it to work but it just appended one set of data to the bottom of the next

- that's what union queries do. They also delete duplicates in the process.