Link to home
Get AccessLog in
Avatar of rito1
rito1

asked on

SQL Syntax Help - Merging 2 tables

Hi All,

Can you help me with some syntax..

I have the following 2 tables which are clearly different other than RegID and RegEID which in some instances they store the same values:

Table1

RegID   Title    Unit
1           Test    2
2            Prep   1
3            Start   1

Table2
RegEID     Capacity    Date
1               1.6             02/01/2009
3                2.2            10/12/2010
5                4.0             01/02/2011

What I need to achieve is I need to return all records from both tables whether RegID and RegEID match or not. If they do match then these records need all show on the same row like so:

RegID/RegEID     Title          Unit             Capacity         Date
1                         Test          2                 1.6                 02/01/2009
2                         Prep          1                 NULL             NULL
3                         Start          1                 2.2                10/12/2010
5                         NULL        NULL           4.0                 01/02/2011


Can anyone provide any guidance on this?

Many thanks,

Rit
Avatar of HainKurt
HainKurt
Flag of Canada image

try

select *
from table1 t1 full join on table2 t2 on t1.regid=t2.regeid
oops a tyoe above, should read as:

select *
from table1 t1 full join table2 t2 on t1.regid=t2.regeid
Avatar of rito1
rito1

ASKER

Thanks HainKurt. I am just trying it now.

Rit
if you want only one id


select ISNULL(t1.id,t2.id) as RegID, t1.*, t2.*
from table1 t1 full join table2 t2 on t1.regid=t2.regeid
order by 1
of course id should replace with regid/regeid

select ISNULL(t1.regid,t2.regeid) as RegID, t1.*, t2.*
from table1 t1 full join table2 t2 on t1.regid=t2.regeid
order by 1
Avatar of rito1

ASKER

select *
from table1 t1 full join table2 t2 on t1.regid=t2.regeid

I currently get syntax error on the FULL keyword. Any ideas?... Just to confirm, I am running this SQL in Access 2007.

Thanks,

Rit
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Lowfatspread
where does the sql server come into the process then?

please remoive the topic area if it doesn't apply
Avatar of rito1

ASKER

Sorry about the inclusion of the SQL Server topic. Yes this is incorrect and should be for Access. I cant seem to remove it.

Rit
Avatar of rito1

ASKER

HainKurt,

I am just trying the UNION JOIN theory now.

Rit
try this one too

select * from table1 t1 left join table2 t2 on t1.regid=t2.regeid
union
select * from table1 t1 right join table2 t2 on t1.regid=t2.regeid WHERE t1.RegID is null


or filtering the cross join if the tables are relatively small

select * from table1 t1, table2 t2 where (t1.RegID=t2.RegEID or t1.RegID is null or t2.RegEID is null)
Avatar of rito1

ASKER

Brilliant. Thanks for your help.

Rit