Join Issue. please help...

[bsnapool]

Hi ,

I have two tables: I currently have an outer join getting all the data from Act table where fields match in both tables.

How do i get it so if the table 2 does have data which I want to show and table 1 does not but still matches on my joins.

How do i get it show in the query results?

Any help much appreciated.

E.g

Table1 Table2
dog spider
cat cat
puppy dog
mouse mouse

How would i get the the record spider to go in the report and from and say there 1 spider table 2 and o spider in table 1?

This make sense?

 

[tehNellie]

Use a LEFT or a RIGHT OUTER join (depending on where the data is).

SELECT tbl1.[aColumn], tbl2.[acolumn]
FROM tbl1
LEFT OUTER JOIN tbl2 ON
  tbl1.column = tbl2.column

Will show you all results from tbl1 and anything from tbl2 that matches on the Join. Where there is no match, tbl2.[acolumn] will be NULL. If you only want the information where tbl2 is a match you can and possibly should use an INNER JOIN instead, but you can just add "WHERE tbl2.[acolumn] IS NOT NULL" to your criteria.

If you change the LEFT to a RIGHT join, you'll get the opposite effect.

Typically I only save LEFT joins but during testing etc it can be handy to switch a LEFT to a RIGHT join and see what is going on from the opposite perspective.

 

[bsnapool]

Hey

Thanks for your reply. I have tried different joins and still not reporting activity from the 2nd table where there is not data in the 1st table.

I wondering if there is a need to do a union on these two tables? You think that would do it?

 

[neileg]

You need two queries, one with a left join and one with a right. The use a union to join these together. Use UNION and not UNION ALL otherwise you will get duplicates.

 

[bsnapool]

Sorry I dont get what you mean?

 

[bsnapool]

Should I select all from table 1...query it... and add conditions....

Should I select all from table 2...query it... and add conditions....

Then create a third query joins them two together?

 

[neileg]

Query 1 should return all the records from table 1 and those records from table 2 that match.
Query 2 should return all the records from table 2 and those records from table 1 that match.
Query 3 is a union query that adds the records from query 1 and 2 together.

 

[bsnapool]

Hi Neil,

Thanks for your reply. Sorry but Im still having problems understanding. Should the only columns which match be the joins? because some of the fields are different? And I have had to use reference tables to do some lookups to match fields? It is possible i could send you an example?

Any more help would be really really appreicated.

 

[neileg]

My Access is broken so I can't open an mdb. You could post the SQL of the queries you have written and that might be enough.

 

[bsnapool]

Could i post a word doc with screen shots of what i need and what i already got??

Think i just need a starter then i could plod along??

Thanks for ur help..

 

[tehNellie]

If you're going to use a Union, the columns in the results of the first two queries need to be identical, you can't do

SELECT [columna], [columnb]
FROM table1/query1
UNION
Select [columna],[columnb],[columnc]
from table2/query2

For example, although you can use different columns and Alias them in the results:

SELECT [Columna] as Test
FROM table1
UNION
SELECT [Columnc] as Test
FROM table2

 

[bsnapool]

What would you do if you needed reference tables to be included?