|
On reflection, I think you're right and im wrong. However, in practice I wouldnt eliminate either as Id like to get either the price or the item from my sales table.
|
|
|
|
|
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
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
|
|
|
|
|
That works perfectly. Thanks very much Syed
|
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
|
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[ ^]
|
|
|
|