Problem with FULL OUTER JOIN - Database Discussion Boards

Yeah, makes sense that both fk:s are present!

The need to optimize rises from a bad design.

My articles[^]

Thanks a lot J4mieC and Ashfield!
This is exactly what ive been looking for!

Best regards.

Good Afternoon

I have a Table that get recreated from multiple tables, When its time to recreate it, i found that there are other views that are accssing the table and i cant delete it. in my SP i normaly find out if it exists and drop it like this

if exists (select * from dbo.sysobjects where id = object_id(N'[sde].[PROPERTY_SUMMARY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [sde].[PROPERTY_SUMMARY]

And the Select into Statement will follow. How do i drop the Table even there are references to this table.

Thank you

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

You have to drop the references first. Why are you dropping it each time - can you not just delete the contents?

Thank you very much, done as you adviced and everything is cool

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

I have got to agree with Paddy
Delete or truncate the table rather than drop it. You should never drop a table as part of a process.

Never underestimate the power of human stupidity
RAH

Thank you very much, done as Paddy adviced and everything is cool Smile | :)

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Hi,

I have two tables as follows:

CREATE TABLE [dbo].[WebSyncHistory](
	[UserID] [int] NOT NULL,
	[SyncTableName] [nvarchar](50) NOT NULL CONSTRAINT [DF_WebSyncHistory_SyncTableName]  DEFAULT (''),
	[SyncTime] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[WebSyncTimesMS](
	[UserID] [int] NOT NULL,
	[SyncTableName] [nvarchar](50) NOT NULL CONSTRAINT [DF_WebSyncTimesMS_SyncTableName]  DEFAULT (''),
	[LastActionTime] [datetime] NOT NULL
) ON [PRIMARY]

I have some records in each of these tables:

INSERT INTO WebSyncHistory VALUES (1, 'Products', '01/01/2008')
INSERT INTO WebSyncHistory VALUES (1, 'Categories', '01/01/2008')
INSERT INTO WebSyncHistory VALUES (2, 'DeviceSettings', '01/01/2008')

INSERT INTO WebSyncTimesMS VALUES (1, 'Products', '31/08/2008')
INSERT INTO WebSyncTimesMS VALUES (1, 'DeviceSettings', '01/01/2008')
INSERT INTO WebSyncTimesMS VALUES (2, 'DeviceSettings', '31/08/2008')
INSERT INTO WebSyncTimesMS VALUES (2, 'Categories', '01/01/2008')

Each table has two fields with the same name and data type
What I want to do is
select all records from each table in such a way that I end up with 4 columns - UserID, SyncTableName, SyncTime, LastActionTime

UserID	SyncTableName	SyncTime        LastActionTime
1	Products	01/01/2008	31/08/2008
1	Categories	01/01/2008	NULL
1	DeviceSettings	NULL		01/01/2008
2	DeviceSettings	01/01/2008	31/08/2008
2	Categories	NULL		01/01/2008

The closest I can get to what I want is the following:

SELECT [WebSyncHistory].[UserID]
      ,[WebSyncHistory].[SyncTableName]
      ,MAX([WebSyncHistory].[SyncTime]) As SyncTime
      ,[WebSyncTimesMS].[UserID]
      ,[WebSyncTimesMS].[SyncTableName]
      ,[WebSyncTimesMS].[LastActionTime]
FROM ([WebSyncHistory] FULL OUTER JOIN [WebSyncTimesMS] 
ON [WebSyncHistory].[UserID] = [WebSyncTimesMS].[UserID] AND [WebSyncHistory].[SyncTableName] = [WebSyncTimesMS].[SyncTableName])
	
GROUP BY [WebSyncHistory].[UserID]
      ,[WebSyncHistory].[SyncTableName]
      ,[WebSyncTimesMS].[UserID]
      ,[WebSyncTimesMS].[SyncTableName]
      ,[WebSyncTimesMS].[LastActionTime]

which returns:

UserID	SyncTableName	SyncTime			UserID	SyncTableName	LastActionTime	  
NULL	NULL		NULL				1	DeviceSettings	2008-01-01 00:00:00.000
NULL	NULL		NULL				2	Categories	2008-01-01 00:00:00.000
1	Categories	2008-01-01 00:00:00.000		NULL	NULL		NULL
1	Products	2008-01-01 00:00:00.000		1	Products	2008-08-31 00:00:00.000
2	DeviceSettings	2008-01-01 00:00:00.000		2	DeviceSettings	2008-08-31 00:00:00.000

I'm completely stumped with this one. Can anyone suggest an alternative approach?

Thanks very much,

dlarkin77

Looking at the output you want, I dont think you need a "full" outer join since you always want to match UserID and SyncTableName. Here's a solution.

I will use a two-step procedure to solve this:
1. Get unique UserID and SyncTableNames (Creating a CTE will be best here)
2. Have a left outer join with the two tables

--create a CTE
With UserTableMods( [UserID], [SyncTableName])
As 
(
	Select distinct [UserID], [SyncTableName] from [WebSyncTimesMS] 
	union
	Select distinct [UserID], [SyncTableName] from [WebSyncHistory]
)

--Get result
Select UTM.[UserID], UTM.[SyncTableName], H.[SyncTime], M.[LastActionTime] 
From UserTableMods UTM
LEFT OUTER JOIN [WebSyncHistory] H ON UTM.USERID=H.USERID and UTM.[SyncTableName]=H.[SyncTableName]
LEFT OUTER JOIN [WebSyncTimesMS] M ON UTM.USERID=M.USERID and UTM.[SyncTableName]=M.[SyncTableName]

And here's the result:

1	Categories	2008-01-01 	NULL
1	DeviceSettings	NULL	   	2008-01-01 
1	Products	2008-01-01 	2008-08-31 
2	Categories	NULL	   	2008-01-01 
2	DeviceSettings	2008-01-01 	2008-08-31

You can use the Grouping and Max functions as per your requirement. Hope that helps.

Regards,
Syed Mehroz Alam

My Blog
My Articles

That works perfectly. Thanks very much Syed

Glad to help you.

My Blog
My Articles

Hi
I have a column in my database named group. However when I put that column in a stored procedure the word 'group' highlights blue and I cant declare it as a column because I think it means something different for sql.
Any help would be great

thanks

Deliver yesterday, code today, think tomorrow.
"http://www.heuse.com/cphumor.htm"

Surround it with square brackets.

Giorgi Dalakishvili

#region signature
My Articles / My Latest Article[^] / My blog[^]
#endregion

Cheers found that on another article, but thanks for the answer!

Deliver yesterday, code today, think tomorrow.
"http://www.heuse.com/cphumor.htm"

Glad to help you Smile | :)

Giorgi Dalakishvili

#region signature
My Articles / My Latest Article[^] / My blog[^]
#endregion

Dear All,
I have a simple query, and I need to change the returned data.
I mean if some row has a value "Bnk", I need to return this value as "BankDep".
Is this possible in SQL 2005?
This is my query:
SELECT INDEX_CODE from TableTest;
The result: INDEX_CODE
Default
Int
Bnk
Inv
I need to display the previous values as
INDEX_CODE
Default123
International
BankDep
Investment

Kind Regards
OBarahmeh

select
case INDEX_CODE
when 'Default' Then 'Default123'
when 'Int' Then 'International'
when 'Bnk' Then 'BankDep'
When 'Inv' Then 'Investment'
END
from TableTest

Thank you very much,
I did not knot that there is a CASE option in SQL
Thanks a lot

Kind Regards
OBarahmeh

The first answer will work, but you might want to consider making this a lookup column linked to a table with a descriptive name for your codes - bit more maintainable.

hi...
I have one txt file from d:,I want to retrieve that file using Select query in SQL.anybody knows please reply me...Thanks

Raaj

what database are you using.

Look at OPENROWSET, I think that allows you to get at a file using select.

Never underestimate the power of human stupidity
RAH

SQL Server 2005

Raaj

I am using Visual Studio 2005 and SQL Server 2005+SQL Server MAnagement Studio 2005.i m using Windows Vista home.
when in "SQL Data source" i configure it ,then after selecting "NewConnection",
i select "Sql Client".
then select the Server (.).
But then in the "Database" Dropdownlist,I couldnt find any of the databases i had made SQL Server MAnagement Studio 2005.
i am new to SQL Server 2005.Please help me out...
thanx in advance

It could be that SQL Server Browser service is not running.

The need to optimize rises from a bad design.

My articles[^]

Are you trying to connect/attach a new database or connect to a new instance of SQL Server?

“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan