|
I think query have to be something like this:
select table1.*,table2.*,table3.*<br />
from table1,table2,table3<br />
where table2.id = table1.id and table2.id = table3.id
Hope it will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
You can do it like this:
select t1.*, t2.*, t3.*
from table1 t1
inner join table2 t2 on t2.table1_id = t1.id
inner join table3 t3 on t3.table2_id = t2.id
where t1.field1 = <something>...</something>
These are just standard joins.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Do not delete messages after they are answered!
The idea is that people can find answers from these forums based on keywords in both questions and answers.
|
|
|
|
|
Mika Wendelius wrote: Do not delete messages after they are answered!
The idea is that people can find answers from these forums based on keywords in both questions and answers
This why, if possible, I'll highlight their inquiry so it shows up in the {so-and-so} wrote:. If it is in the post of the first person who answers, then the question pretty much stays there unless Chris gets a hamster to zap the entire thread.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Good point. Personally I must learn to use that technique more often (started immediately to quote on a new thread  )
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Like Mika said, don't delete your message even if it has been answered. Others may want to chime in their thoughts/ideas later on, or learn from a question you have that was answered.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
hi,
I have created C++ application which create DSN with ODBC Microsoft text driver to use .csv file
as Database .
I want to change data type of some columns.So I tried
cmdstr = "ALTER TABLE table1 ALTER COLUMN RATE char(20)";
SQLExecDirect(hstmt, cmdstr, SQL_NTS);
But its give error as "syntax error in Alter Statement".
When we create DSN using ODBC wizard schema.ini file generate which contains the schema
information.But When I generate DSN by programmatically Schema.ini file not generate.
Pointer in this direction will be helpful for me.
Sunil
|
|
|
|
|
|
try using:
ALTER TABLE table1 modify COLUMN RATE char(20)
|
|
|
|
|
I wan to search sql server table to find any word contains some all the letter what should i do.
I'm using Asp.net vb to create web page. I create a text box I want to search for special letters.
I'm using sqldatasource to connect to sql server with the following procedure:
SELECT Name, Emai FROM Item WHERE (itemName = @itemName)
What should i do instead of (itemName = @itemName)
Thanks
|
|
|
|
|
If it is letters in a series (rather than any word containing any of these letters) you can use
WHERE ItemName like '%xyz%'
|
|
|
|
|
As in previous post use LIKE for simple pattern matching or if you need more logic for the search, use CONTAINS[^] predicate.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks for this help.I'm trying apply it on the asp.net web page there was an error occur.
cannot use a contains or free text predicate on table or indexed view "item" because it is not full-text indexed
What should i do
thanks
|
|
|
|
|
y_mmohd wrote: cannot use a contains or free text predicate on table or indexed view "item" because it is not full-text indexed
therefore you can either create a full-text index on the column OR use the LIKE expression - as you have been told several times.
y_mmohd wrote: I'm trying apply it on the asp.net web page
is totally irrelevant, its your sql query that is wrong.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
I've got a query like this:
WITH CTE1 AS
(
SELECT * FROM TABLE WHERE A = @A
),
CTE2 AS
(
SELECT * FROM TABLE WHERE B = @B
)
SELECT * FROM
CTE1
CROSS JOIN
CTE2
The above query works fine for me and always returns cross join result of the two queries. The problem happens when one of those CTEs doesn't return any row and therefore the CROSS JOIN returns nothing as well. When only one of those CTEs return something, my expectation is to return the result of that CTE cross joined with null values. Do you know how I can achieve this purpose?
|
|
|
|
|
Since cross join doesn't have any 'OUTER' option, the result is always all combinations from all sources. If any of the sources have 0 rows the size of the result set is 0.
I think you could use simple outer joins and unions to get desired rows. Something like:
SELECT *
FROM Table A LEFT OUTER JOIN Table B ON 1=1
WHERE ...
UNION
SELECT *
FROM Table A RIGHT OUTER JOIN Table B ON 1=1
WHERE ...
However, I would re-check the design of the data model since normally the need to cross join is very rare and may indicate a design problem.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi All
I am writting an stored procedure to get the recently inserted records primary key as follows:
ALTER PROCEDURE [DBO].[AddEditUserMsgDetails]
(
@iJobID [INT]=NULL,
@iUserID [INT]=NULL,
@vFromName [VARCHAR](50)=NULL,
@vFromAddress [VARCHAR](50)=NULL,
@vSubject [VARCHAR](50)=NULL,
@iListID [INT]=NULL,
@vActualText [VARCHAR](100)=NULL
)
AS
BEGIN
IF @iJobId IS NULL OR @iJobId=0
BEGIN
INSERT INTO ezlMailJobs (iUserID,vFromName,vFromAddress,vSubject,iListID,vActualText)
VALUES (@iUserID,@vFromName,@vFromAddress,@vSubject,@iListID,@vActualText)
set @iJobID= @@Identity
select @iJobID
END
IF @iJobId IS NOT NULL
BEGIN
UPDATE ezlMailJobs SET iUserID=@iUserID,vFromName=@vFromName,vFromAddress=@vFromAddress,
vSubject=@vSubject,iListID=@iListID,vActualText=@vActualText
WHERE iJobId=@iJobId
END
IF @@ERROR=0
BEGIN
COMMIT TRAN
SELECT 1
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT -1
END
As in the above stored procedure when ijobid is null or zero, It should execute insert statement and return recently inserted records ijobid else it should execute update statement. But as now it is executing insert statement and returning some garbage value. one more thing I have declared ijobid as identity.
I am unable to figure out the problem. Please help me.
Thanks
WAA
|
|
|
|
|
If the jobid is null or 0 you are executing an insert AND an update
IF @iJobId IS NULL OR @iJobId=0
BEGIN
..do insert
<big>set @iJobID= @@Identity</big>
select @iJobID
END
IF @iJobId IS NOT NULL ------ YOU SET IT 2 LINES ABOVE
BEGIN
...do update etc
Also you have commit/rollback, but I don't see a begin tran?
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks for your reply
I found the solution, want to share with the needful.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [DBO].[AddEditUserMsgDetails]
(
@iJobID [NUMERIC]=NULL,
@iUserID [NUMERIC]=NULL,
@vFromName [VARCHAR](50)=NULL,
@vFromAddress [VARCHAR](50)=NULL,
@vSubject [VARCHAR](50)=NULL,
@iListID [NUMERIC]=NULL,
@vActualText [TEXT]=NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Return [NUMERIC]
SET @Return=NULL
BEGIN TRAN
IF @iJobId IS NULL OR @iJobId=0
BEGIN
INSERT INTO table1 (iUserID,vFromName,vFromAddress,vSubject,iListID,vActualText)
VALUES (@iUserID,@vFromName,@vFromAddress,@vSubject,@iListID,@vActualText)
SET @Return= SCOPE_IDENTITY()
SELECT @Return AS iJobId
END
IF @iJobId IS NOT NULL
BEGIN
UPDATE table1 SET iUserID=@iUserID,vFromName=@vFromName,vFromAddress=@vFromAddress,
vSubject=@vSubject,iListID=@iListID,vActualText=@vActualText
WHERE iJobId=@iJobId
SELECT 0
END
IF @@ERROR=0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT -1
END
SET NOCOUNT OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
|
|
|
|
The best way to do this, in my opinion, is to use the SCOPE_IDENTITY() function. This function will return the most recent identity value created in the current scope. Returning the value for the current scope is important because if you have triggers firing that perform inserts the @@IDENTITY variable will return the last identity value created, even if it was from a different table because of a trigger. You should use:
set @iJobID = SCOPE_IDENTITY()
after the insert.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Thanks a lot. Its working now.
|
|
|
|
|
I read an article here[^] describing how to perform SQL operations in an atomic manner using transactions. I just do not understand what the usage of a transaction is in the following example (I didn't ask my question below that article because the author has not answered asked questions).
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
AS
BEGIN TRAN
INSERT titles(title_id, title, type)
VALUES (@title_id, @title, @title_type)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
INSERT titleauthor(au_id, title_id)
VALUES (@au_id, @title_id)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
COMMIT TRAN
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1
My question is, if the the first insert statement failed, why do we need to roll back it? No insertion to database happened, therefore we do not need to rollback anything! If I'm right, what's the problem with the following sproc:
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
AS
INSERT titles(title_id, title, type)
VALUES (@title_id, @title, @title_type)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
INSERT titleauthor(au_id, title_id)
VALUES (@au_id, @title_id)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
RETURN 0
ERR_HANDLER:
RETURN 1
|
|
|
|
|
In your version if the second insert fails, then an orphaned title has been added without its associated author.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
