Hi........ very urgent join question

[shripaldalal]

Hi,

I have two tables

table1:
========================
name sirname
===== ======
shripal dalal
deepak dalal

table2:
========================
name sirname
===== ======
ramesh dalal

now if i do a left join/inner join/right join (watever, i will take
left join as an example):

select table1.name, table2.name from table1 left join table2 on
table1.sirname = table2.sirname i get:

table1.name table2.name
========= ==========
shripal ramesh
deepak ramesh

now the above is wat i dont want.........

i want something like this:

table1.name table2.name
========= ==========
shripal ramesh
deepak <blank>

basically i just want to CONCATENATE TWO RECORDSETS....

something like:

select table1.name from table1 + select table2.name from table2 but
where sirname is equal in both tables

is this possible ?

regards,
shripal dalal.

 

[Guest]

What you are getting is the correct output because depak is linked to ramesh.
I think what you want is a Union Query which allows you to do a query and
append another query result. You wouldn't link the tables.

Hope this leads you in the correct path,
James

 

[John W. Vinson]

basically i just want to CONCATENATE TWO RECORDSETS....

something like:

select table1.name from table1 + select table2.name from table2 but
where sirname is equal in both tables

is this possible ?

Yes: use the UNION operator. You need to be in SQL view to do so; the
query grid can't do it.

SELECT [Name] FROM [table1]
UNION ALL
SELECT [Name] FROM [table2]

will take 1000 records from Table1, and 2000 from Table2, and give you
3000 records as a result.

If you use UNION instead of UNION ALL Access will remove all duplicate
records in the process, so you'll only get the (say) 2317 unique
names.

John W. Vinson [MVP]