|
Here's one possibility:
<br />
DECLARE @Person TABLE(PersonId INT, Name VARCHAR(10))<br />
DECLARE @Details TABLE(PersonId INT, Phone VARCHAR(10))<br />
<br />
INSERT INTO @Person VALUES (1, 'John')<br />
INSERT INTO @Person VALUES (2, 'Mary')<br />
<br />
INSERT INTO @Details VALUES (1, '8005551234')<br />
INSERT INTO @Details VALUES (1, '8665554321')<br />
INSERT INTO @Details VALUES (2, '8005558888')<br />
INSERT INTO @Details VALUES (2, '8665559999')<br />
<br />
SELECT * FROM @Person P<br />
INNER JOIN @Details D ON D.PersonID =P.PersonID<br />
<br />
SELECT P.PersonId, P.Name, D.Phone, D2.Phone FROM @Person P<br />
INNER JOIN @Details D ON D.PersonID = P.PersonID<br />
LEFT JOIN @Details D2 ON D2.PersonId = D.PersonId AND D2.Phone <> D.Phone<br />
WHERE D.Phone IN (SELECT TOP 1 Phone FROM @Details WHERE PersonId = P.PersonID ORDER BY Phone)<br />
<br />
Although, if there were anyway for you to modify the schema so each phone number had either some sequence number or a phoneType column which was unique w/in the scope of the MemberId it would be easier to write something that would perform a lot better. This will work with small to medium size tables, but not on a very large table with millions of rows.
|
|
|
|
|
iam passing a parameter in oracle then how ican know that the parameter is containg to_char or not
|
|
|
|
|
Here's my query:
SELECT TOP (1) COUNT(*),[Destination Telephone No], [Call Start Date]
FROM Calls
WHERE ([Destination Telephone No] IS NOT NULL) AND (Name = @name) AND ([Call Start Date] >= @start) AND ([Call Start Date] <= @end)
GROUP BY [Destination Telephone No], [Call Start Date]
ORDER BY Count(*) DESC
--@name, @start, @end are controls which I bind results to
Any help would be appreciated!
|
|
|
|
|
If you put this query in your database server, what does it return ?
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
no results...But I think I finally figured it out. I changed the "name = " to "name like' and it seems to be working now! not sure why as the name is exactly equal to the name in the database?! but anyways it works now
|
|
|
|
|
Daniel_Logan wrote: as the name is exactly equal to the name in the database
Is it? or are there trailing spaces - I've been caught by that before
Bob
Ashfield Consultants Ltd
|
|
|
|
|
No, there where no trailing spaces hey. I bound a drop down list to the table and pulled out the names and filled them into the list. Then when I clicked a button I called the dropdrownlist and got the name directly from there. So it was exactly the same. When I changed that to "like" it made all the difference?! not sure why or how that works but it worked!
|
|
|
|
|
i agree. there was a similar case with me also a few days ago
Ashish Sehajpal
|
|
|
|
|
Hi all
Iam using an SSIS package to import existing data from Oracle tables to SQl 2005 tables. I need to remove the identity from the sql table import the data then set the udentity again to the sql table.
Any ideas?
|
|
|
|
|
Hi,
The only way I know of to turn off the identity property is to modify the system tables. Try this:
------------------------------------------------------------------------
sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('yourtable')
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go
------------------------------------------------------------------------
Put in yourtablename and youridentitycolumn names. Once this runs, do your update on the column, and then set the identity property back on:
------------------------------------------------------------------------
sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it's an identity column */
where id = object_id('yourtable')
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go
------------------------------------------------------------------------
Hope this helps .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
You can use
SET IDENTITY_INSERT tablename ON
to insert explicit values into your identity column. Once your import is done, do
SET IDENTITY_INSERT tablename ON.
Remember, SQL Server will continue from the last known value inserted into the identity column.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi, thanks for looking
I would like to create an index with 2 columns
I have an addresses table
AddressID, UserID, Name, ...
Each user can have many addresses, a user can not have two addresses with the same name
I have a special type of address, where 0 is set as the UserID (They dont belong to a user, but to something else)
Is there any way to create an index that wont throw an error beacuase i have many address with 0 as the UserID and "Special Address" as the Name
How could i acoomplish this?
Or are Triggers my only choice?
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
|
Thanks for the suggestion
I already know how to create an index with n columns
My problem is not that
I want an index for two columns
Ignoring duplicates when column1 = 0
Do you know any way to do that with indexes
Thanks
Alexei Rodriguez
|
|
|
|
|
Nope, conditional exclusion is not possible. If you're creating a unique index, SQL Server will look for uniqueness across both the columns anyway.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Thanks for your anwer
It look like triggers are my other option
I was trying this inside a trigger, but it doesnt work as expected: When trying to modify, i get the Duplicate error even if i give a name that doesnt exist in the table at all
<pre>
IF EXISTS (SELECT * FROM Inserted AS I JOIN Dirs AS D ON I.DirID = D.DirID AND I.Name = D.Name)
BEGIN
ROLLBACK TRAN
RAISERROR('Duplicate',16,1)
END
</pre>
DirID is the primary key, and Name is a description for the given address
Thanks in advance for any feedback
Alexei Rodriguez
|
|
|
|
|
Is it a AFTER trigger or an Instead of trigger? You ought to use Intead of Triggers inside which you have to check if the name already exists and explicitly insert if it does not exist.
If the above trigger is an AFTER trigger, SQL Server will insert the row first, and then fire the trigger, in which case the inserted name will already be present and your EXISTS will return true.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi Guys,
Is there a way wherein I can write an MSSQL script with Distinct command which is good only for one field?
Example:
ITEM Table
ItemCode Description UnitCost
00000001 Center Table $500
00000001 Center &400
00000002 Chair &20
00000002 Chair white &20
Based on the example above, how can I select a records that the ItemCode will only display once?
Result should be like this:
ItemCode Description UnitCost
00000001 Center Table $500
00000002 Chair &20
I tried this SQY Query statement but it doesn't work:
Select Distinct(ItemCode), Description, UnitCost
From Item
Order By ItemCode
Thanks in advance Guys
hifiger2004
|
|
|
|
|
select distinct itemcode,<br />
(select top 1 description from table1 as i1 where i1.itemcode = table1.itemcode) as description,<br />
(select top 1 UnitCost from table1 as i1 where i1.itemcode = table1.itemcode)as UnitCost<br />
from table1
I Love SQL
|
|
|
|
|
Perfect!
Thanks man
hifiger2004
|
|
|
|
|
my pleasure to help u...
I Love SQL
|
|
|
|
|
I am new to using sql and am stuck with a problem. I am trying to pass 2 parameters into an SQL stored procedure. If the 2 parameters exits I want to return all the information within that row to a datatable. The problem is after lookin at many tutorials online the errors keep coming up that my syntax is incorrect below is the stored procedure if anyone can help me it would be greatly appreciated.
CREATE PROCEDURE dbo.Authenticate_User
@Username varchar(50),
@Password varchar(50)
AS
IF EXISTS
BEGIN
(SELECT * FROM Users WHERE User_Username = @Username AND User_Password = @Password)
END
END IF
RETURN
the syntax erros are:
Incorrect Syntax near the keyword 'BEGIN'
Incorrect Syntax near the keyword 'IF'
|
|
|
|
|
EXISTS works like this:
<br />
IF EXISTS(SELECT * FROM Users WHERE User_Username = @Username AND User_Password = @Password) BEGIN<br />
SELECT * FROM Users WHERE User_Username = @Username AND User_Password = @Password<br />
END<br />
But there is no reason to use it in this case. Most often it is used like this to determine if an UPDATE (record exists) or an INSERT (does not exist) should be done.
If you just change your procedure to this:
<br />
CREATE PROCEDURE dbo.Authenticate_User<br />
@Username varchar(50),<br />
@Password varchar(50)<br />
AS<br />
<br />
SELECT * FROM Users WHERE User_Username = @Username AND User_Password = @Password<br />
<br />
Then you will exactly the same result as with the EXISTS, but you won't be wasting resources querying for the record twice.
|
|
|
|
|
As per subject,
I'm starting to code my database.
Is there a front end GUI app that can assist me with creating, editing and gettting a good view of my tables?
When I was in school, I used to use something called mySQL Enterprise manager, but that seems to be a tool for purchase only.
Suggestions on a free solution?
|
|
|
|
|