|
Hello every one!
Well I have a question here about the self joined table. Well I know how it works theoretically but I’m working on it for the first time in SQL Server 2000. I’m trying to build a data entry form for it and now I’m a bit confused that how do we manage the Foreign Key of that table. I don’t know if I’m putting this question in the rt manner. How do we make the forms over a self joined table. Plz answer that having the auto-increment PK in mind. I’m working in .net 2.0 C# Windows Forms.
Thanks in advance…
Rocky
|
|
|
|
|
I'm not an expert in this but since you've had no other replies, here is my opinion...
Just create your form as if the underlying table didn't have a self join.
It's purpose is to relate two fields in the same table and is really only to make data retrieval (queries, SP's etc) easier and more efficient.
Steve
|
|
|
|
|
hello every one,
well I'm working on my first professional DB project, and this database has got inventory managmnt, billing, ledgers, and employee mgt modules. So for now I've made the main page very simple. Its got a tab control (I'm working in .net 2.0 C#) with two tabs 1 for forms and the other for reports with each tab having the links to the forms. But it doesnt look very elegant.
right now I'm just short of ideas of how to make the mian page and what sort of format I should give to the forms. can u plz suggest a few good looking and easily navigatable designs. or is there any website that gives u a few samples of good looking UIs
thanks in advance...
Rocky
|
|
|
|
|
Hi
Is it good practice to return multiple result set from single stored procedure when all result set will be used to populate a single page.
e.g.
CREATE PROCEDURE WHYNOT
AS
BEGIN
SELECT col1,col2 from table1
SELECT col3,col4 from table2
SELECT col5,col6 from table3
PS: 1) These are simple statements, but i will have joins on each query....
Please advice and guide.
Amit
|
|
|
|
|
It isn't necessarily a bad idea however depending on the amount of data returned it could be. Making fewer trips to the database can be a benefit but if the results returned are very large it may be better to break up the requests.
only two letters away from being an asset
|
|
|
|
|
Hi
I have three tables like parent and child and I don’t know in child table how many records can be. E.g.
[TB_ProductMaster]
[ProductId] [varchar] (4) NOT NULL ,
[ProductTitle] [varchar] NOT NULL ,
Many other fields
Data Looks like
A1 TV
A2 VCR
A3 HT
[TB_InstructionMaster]
[InstructionId] [int] IDENTITY (1, 1) NOT NULL ,
[InstructionDesc] [varchar] (50) NOT NULL
Data Looks like
1 “Always Top”
2 “Always Down”
3 “Always Left”
4 “Always Right”
[TB_ProductInstruction]
[Id] [int] NOT NULL ,
[ProductId] [varchar] (4) NOT NULL And FOREIGN KEY FOR PRODUCTID
[InstructionId] [int] NOT NULL
Data Looks like
1 A1 1
2 A1 2
3 A1 3
4 A1 4
5 A2 1
6 A2 2
(Please note here i don;t know the child count. e.g product can have one Instruction or can have n numbers of Instructions.)
Now i need output like for supplied product parameter as. This paramter will be input to SP.
Product Id InstructionDesc1 InstructionDesc2 and so on
A1 Always Top Always Down
A2 Always Top Always Down
A3 Null NULL
A4 NULL NULL
Here so on means , n number of columns can be....
I know by using LEFT OUTER join i can do the needful but only if I know the count in my child table. But in my case we don't know  Moreover because of our company database standards we can not use CURSORS also....
We need to do this by using query or in worest case dynamic SQL...
PLEASE HELP ME……….PLEASE HELP ME……….PLEASE HELP ME……….
Amit
|
|
|
|
|
Too bad you can't use a cursor:
DECLARE @iColumns INT, @sql VARCHAR(2500),@cInstruction VARCHAR(50), @nInstruction int
SET @sql = ''
DECLARE curInstructions CURSOR FOR SELECT DISTINCT InstructionDesc,InstructionID FROM TB_InstructionMaster
OPEN curInstructions
FETCH NEXT FROM curInstructions INTO @cInstruction,@nInstruction
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', (SELECT ''' + @cInstruction + ''' as fld
FROM TB_ProductInstruction P2
WHERE P2.ProductID = ProdInst.ProductID and InstructionID = ''' +
cast(@nInstruction as varchar) + ''') as [' + @cInstruction + ']'
FETCH NEXT FROM curInstructions INTO @cInstruction,@nInstruction
END
close curInstructions
DEALLOCATE curInstructions
SET @sql = 'SELECT distinct ProdMast.ProductID ' + @sql + '
FROM TB_ProductInstruction ProdInst
right join tb_productmaster ProdMast on ProdInst.ProductID = ProdMast.ProductID'
exec(@sql)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi all,
Here's the problem...
I've got two tables, trains and trainarrivals. Each train has a link to two arrivals records. The first link is to the previous arrival record and the d=second is to the next arrivals record.
I'm trying to join the tables using,
select * from train LEFT OUTER JOIN trainarrivals<br />
ON train.PreviousArrival = trainarrivals.UniqueID<br />
OR train.NextArrival = trainarrivals.UniqueID<br />
ORDER BY train.UniqueID
so that when i get the trains with their arrivals links I will get one row for each train. Due to the fact that the next and previous arrivals are separate records in the trainarrivals table I get two records for each train.
My question is, is there any way to do this so I get only one record with both the next and previous arrival data together.
Thanks for the help in advance.
Rich
|
|
|
|
|
select * from train
LEFT OUTER JOIN trainarrivals ta1 ON train.PreviousArrival = ta1.UniqueID
LEFT OUTER JOIN trainarrivals ta2 ON train.NextArrival = ta2.UniqueID
ORDER BY train.UniqueID
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks Eric!
It worked a treat. I really didn't know it would be that simple.
Thanks again!
|
|
|
|
|
what do we mene by daynamic sql in sql server 2000
and how can iuse them
|
|
|
|
|
Dynamic SQL are those SQL which gets the value at runtime. I am sharing one simple example and i hope this will help you.
Declare
@table Varchar(25),
@Sql Varchar(25)
BEGIN
SELECT @table='TB_Emp'
Select @Sql= 'SELECT Count(*) FROM' + @table
exec (@sql)
END
For more details refer google or SQL Online Help.
Amit
|
|
|
|
|
i have a DataTable. i want to know that one of th e elements is null or not.
if(myDataTable.Rows[row][col] == null)
{
.....
but it doesnt work. what should i do?
|
|
|
|
|
You can use IsDBNull for that purpose.
Best Regards,
Apurva Kaushal
|
|
|
|
|
if(myDataTable.Rows[row][col].ToString() == "")<br />
{<br />
....
|
|
|
|
|
i m tryring to display data in datagridview but
error is occuring "cannot create child list of ChartofAcct"
GrdChart.DataSource = cr.GetDataSet;
GrdChart.DataMember = "ChartofAcct";
|
|
|
|
|
Put it in the correct forum. There you will get more correct and quich response.
And also put some more description of the problem. 
Best Regards,
Apurva Kaushal
|
|
|
|
|
what are advantige and disAdvantige of using the
cursor
|
|
|
|
|
|
Cursors are having its own pros and cons.
+Ve : Row wise operation which helps us to avoid lot of complex queries
-Ve : VERY IMPORTANT, needs huge memory of SQL Server. So be careful while using cursor. It is always good if you avoid cursors as much. We have a product which has around 7500 stored procedure and we have written without any cursors )
There are number of ways to avoid cursor....
1) Use While Loop
2) Use Update with FROM Clause
3) Use Joins, use subqueries..
4) Store data is views etc...
5) Last and least do good database desiging.....
Amit
|
|
|
|
|
I think u r right we should avoid cursors as far as possible...I have a query of Dynamic sql how can I use it in a while loop?please help
|
|
|
|
|
hi, i am using MSSQL as my backend, i want to fetch top 3 students from the Student table.Plz help me
Senthil
|
|
|
|
|
This was already answered yesterday in other forum have you checked that?
Best Regards,
Apurva Kaushal
|
|
|
|
|
use top operator. SELECT TOP 3 from table [order by field]. Please note order by clause is optional and if require use it.
Amit
|
|
|
|
|
Hello everybody
I have a problem with SQL Server 2005 Express. Here are description:
In a database, there is a table, name TEST with three columns: a(int), b(int), c(char).
I created a view as the following:
<br />
CREATE VIEW [dbo].[view_test]<br />
AS<br />
SELECT TOP (100) PERCENT A, B, C<br />
FROM dbo.Test<br />
ORDER BY A, B<br />
Clearly, I want the result set ordered by column A and B.
Then I executed the folowing query:
<br />
SELECT * FROM view_test<br />
But the result is not sorted.
In SQL Server 2000, the problem does not happend.
Is there any comment?
Thanks so much.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
