combine current and audit data....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

combine current and audit data....

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-05 : 03:15:24

Hello...

I have a table that contains current data and another that matches the schema that contains historical or audit data. The columns are the same for both tables.

I am trying to join the two tables in a union so that I can get a combined view...but that only shows unique rows - there is an ID column that is a PK on the current table - and that shows the current values from the "live" table.

For example:
Table A
CustID Status
100 Online
106 Online
121 Inactive
145 Online
155 Inactive
212 Waiting

Table Audit_A
CustID Status
121 Online
145 Waiting
155 Online
212 Online

What I am trying to do is to query the two tables and return a result set based on a particular status...but I need to show the row's current Status value from Table A, not Audit_A.

So, if I ran a query for all Cust with a Status of 'Online', I'd like to see (from the above data):
Results
CustID Status
100 Online
106 Online
121 Inactive
145 Online
212 Waiting

I have this query, that only partially works...as it returns 'Online' for all status values.

Select Distinct(CustID), Status
FROM (Select CustID, Status FROM TableA where Status = 'Online'
UNION ALL Select CustID, Status
FROM TableA_Audit where Status = 'Online') AllOnlineCust

Is it possible to get the value from the current table, TableA?

Thanks for any help or suggestion.

- will

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 03:46:21

is this what you want ?

create table #Table_A
(
	CustID	int,
	Status	varchar(10)
)

create table #Audit_A
(
	CustID	int,
	Status	varchar(10)
)

insert into #Table_A
select	 100, 'Online'		union all
select	 106, 'Online'		union all
select	 121, 'Inactive'	union all
select	 145, 'Online'		union all
select	 155, 'Inactive'	union all
select	 212, 'Waiting'

insert into #Audit_A
select	121, 'Online'		union all
select	145, 'Waiting'		union all
select	155, 'Online'		union all
select	212, 'Online'

select	t.*
from	#Table_A t left join #Audit_A a
	on	t.CustID	= a.CustID
where	t.Status	= 'Online'
or	a.Status	= 'Online'

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-05 : 13:22:14

Thanks! That worked great. I had tried LEFT JOIN before...but I didn't have the write WHERE condition and did not get the results I was wanted.

Thanks again.
- will

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources