|
Hi,
I am trying to find an article that clearly explains the various joins in the DataBases Can any one give me the link that explains well about these.
Thanking you in advance,
Satish.
|
|
|
|
|
Lets say you have 2 tables A and B which cotains the following data
A B
-- --
1 1
2 2
4 3
6 4
7 5
8 6
An INNER JOIN will only match rows from two tables where the join condition matches on BOTH sides of the join.
SELECT * FROM A INNER JOIN B ON A.A = B.B
A B
----
1 1
2 2
4 4
6 6
A LEFT OUTER JOIN will return everything on the left side and the matches on the right side leaving nulls if there is no match
SELECT * FROM A LEFT OUTER JOIN ON A.A = B.B
A B
------
1 1
2 2
4 4
6 6
7 null
8 null
A RIGHT OUTER JOIN is similar but it will return everything on the right side and the matches on the left side leaving nulls if there is no match
SELECT * FROM A RIGHT OUTER JOIN ON A.A = B.B
A B
-------
1 1
2 2
null 3
4 4
null 5
6 6
A FULL OUTER JOIN returns everything on both sides leaving nulls if there is no match
SELECT * FROM A FULL OUTER JOIN ON A.A = B.B
A B
-------
1 1
2 2
null 3
4 4
null 5
6 6
7 null
8 null
Does this help you see the patterns that are created by these join types?
DISCLAIMER: I created these results from my head. There may be errors
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
hi Colin Angus,
Thank you very much for the explanation.It is very clear.I heard of few more joins that are equi join,self join Under what category does this belong to and what are they.
Thank you in advance,
Satish.
|
|
|
|
|
A self join is just when you join a table onto itself. There is no special types of join.
Consider a table with a primary key, PK, and a foreign key, FK, and the foreign key refers to the primary key of a different row in the same table.
For instance:
PK FK
-------
1 3
2 3
3 7
4 7
5 8
6 3
7 null
8 null Now, do a join onto itself like this:
SELECT * FROM table AS t1 INNER JOIN table AS t2 ON t1.fk = t2.pk
t1.PK t1.FK t2.PK t2.FK
------------------------
1 3 3 7
2 3 3 7
3 7 7 null
4 7 7 null
5 8 8 null
6 3 3 7
Remember that t1 and t2 are actually the same table, you need the aliases so you can treat the table as if it were actually two separate tables. Once you have the aliased, you can do any type of join you like that you could on two separate tables. Also, thinking about it as being two separate tables, that happen to both hold exactly the same data makes it easier to comprehend.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i get sintax error in this query, why?
query = "UPDATE TmyTable SET Title = '" + labelTitle.Text + "', Text = '" + TextBox1.Text + "', Left = " + this.Left + ", Top = " + this.Top + ", Color = " + panelColor.BackColor.ToArgb() + " WHERE ID = " + id;
the table is made:
[id][title][text][timer][info][Left][Top][Color]
counter | string | string | date | string | int | int | long
|
|
|
|
|
|
Thanks for your advances, i've tried to remove that row but i get the same error also for the other two int value i try to insert, anyway that is the error message:
---------------------------
---------------------------
System.Data.OleDb.OleDbException: Syntax error into UPDATE statement
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at note.nota.nota_Closing(Object sender, CancelEventArgs e) in c:\..\visual studio projects\...\file.cs:line 627
|
|
|
|
|
Colin just told you and I had told you before in some threads below: use parametized queries. You don't have to think about how to format parameters, and also is more secure. Try this code:
string sql = "UPDATE TmyTable SET Title=@Title, Text=@Text," +
" Left=@Left, Top=@Top, Color=@Color WHERE ID=@ID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@Title", labelTitle.Text);
cmd.Parameters.Add("@Text", TextBox1.Text);
cmd.Parameters.Add("@Left", this.Left);
cmd.Parameters.Add("@Top", this.Top);
cmd.Parameters.Add("@Color", panelColor.BackColor.ToArgg());
cmd.Parameters.Add("@ID", id);
cmd.ExecuteNonQuery();Do you see how you don't have to think about enclosing strings in single quotes or if day or month goes first in a date?
The above code is for SQL Server, which uses named parameters. OleDb* classes (Access for example) use unnamed parameters. In the query you just put a question mark, and add parameters to the Parameters collection in the order they appear in the query.
string sql = "UPDATE TmyTable SET Title= ?, Text= ?," +
" Left=@ ?, Top= ?, Color= ? WHERE ID= ?";
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.Parameters.Add("@Title", labelTitle.Text);
cmd.Parameters.Add("@Text", TextBox1.Text);
cmd.Parameters.Add("@Left", this.Left);
cmd.Parameters.Add("@Top", this.Top);
cmd.Parameters.Add("@Color", panelColor.BackColor.ToArgg());
cmd.Parameters.Add("@ID", id);
cmd.ExecuteNonQuery();Oracle also uses named parameters like SQL Server. You have to prepend a colon in the query like in UPDATE TmyTable SET Title=:title, but not when adding the parameter: cmd.Parameters.Add("title", labelTitle.Text). If you are using some other database (mySql for example), search the docs, it's possible and parametized queries will make your life much easier (really!!)
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Thanks for all that you have told me i've used that code and runned the debug but i get the same error.
Maybe it depend by other things about database i don't know... 
What could it depend by?
it works if i don't save left, top and color values.
|
|
|
|
|
i add i get this error only if i try to insert no string value within no string field.
Why i get error if i try to update integer or datetime field while for string i have no problem?
|
|
|
|
|
Maybe the type of the column in the table you are trying to modify is set to varchar instead of int or datetime. Could you give us your table definition?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
i've just wrote it in my first post, anyway it is so:
the table is made:
[id][title][text][timer][info][Left][Top][Color]
counter | string | string | date | string | int | int | long
i get errors when i try to update 4-6-7-8
|
|
|
|
|
I'm not sure about all of them, but some are SQL reserved words... for example Top. In your UPDATE statement, try encolosing each column name in brackets.
UPDATE table SET [Date]=@Date Good luck!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
nothing to do... i don't know really where i wrong!
i know it is a simple thing, but i don't know where is the error, i've tried also to rename the columns name, the parameters name, but nothing!!!
|
|
|
|
|
Please try again to install ur SQL Server then try again.
|
|
|
|
|
how can i insert the picture to the sqldatabase ?what datatype cani use ?
how can i load the picture to the database?can you give me the code with c#.net to insert picture from the webform.asp to the sqldatabase?
|
|
|
|
|
there is the "image" data type in the ms sql server. you can use it to store your images.
cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ImageID", System.Data.SqlDbType.Int, 4))
cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Data", System.Data.SqlDbType.VarBinary, 2147483647))
|
|
|
|
|
thank you
The way you sho me is how to insert the picture to the bank.please help me to load the picture from the bank to the <asp:image>control.
thank you!
|
|
|
|
|
explanation;
I have one table in my sql database called tblStores .it have a column called name .i have created this table by using wizard in the enteprize manager
what is problems:
when i insert rows into the table (using storedprocedure and pass the parameter value in query analyser)only the first character is inserted .if i call this procedure from asp.net this problem apear again.only if i use the datasheet view in enteprize manager the whole word is inserted .
.when i create the script of this table the result was:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStores]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStores]
GO
CREATE TABLE [dbo].[tblStores] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
thank you.
|
|
|
|
|
How are you inserting your data row. Give us the StoredProc. statement that you used.
I Dream of Absolute Zero
|
|
|
|
|
USE abadancity
GO
/* Check whether the sp already exists and
drop it if it does */
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'insertUnit' AND type = 'P')
DROP PROCEDURE insertUnit
GO
-- Create the procedure
CREATE PROCEDURE insertUnit
@unitNameVal nvarchar,
@managerNameVal nvarchar,
@KindActiveval nvarchar,
@shopSpaceVal int,
@typeBuldingval nvarchar,
@cardNumberVal int,
@personNumberVal int,
@addressVal nvarchar,
@phoneNumberVal int,
@faxNumberVal int,
@emailVal varchar
AS
insert into tblstores values(@unitNameVal,@managerNameVal,@KindActiveval,@shopSpaceVal,@typeBuldingval,@cardNumberVal,@personNumberVal,@addressVal,@phoneNumberVal,@faxNumberVal,@emailVal)
|
|
|
|
|
Hello rohollahabadan (hope I got that right),
The sp that you have given here bears no relation to table spec you referred to earlier.
Infact if you try to create that sp within SQL server you will get table mismatch errors.
Maybe you've just posted the wrong sp?
A compatible stored proc would look something like:
<br />
CREATE PROCEDURE insertUnit<br />
@uName nvarchar<br />
as<br />
insert into tblStores values(@uName)
With which you would use something like the following to insert data rows :
<br />
exec insRow 'chin'<br />
GO
-----------------
I Dream of Absolute Zero
|
|
|
|
|
i have give you the stored procedure 2 days ago.please give me your answer!
|
|
|
|
|
When you post a message for help, like this one, remember that a lot of the CP members may not be in the same time zone as you are. Or like me, only have CP access at work, which is why you have not gotten a response from me 'til this Monday morning).
It is frustrating when you are desperate for a response here, but remember that as knowledgable as everyone here is, sometimes you may not get a response (or the one
that you are after). So don't force the issue, if no one gets back to you.
I Dream of Absolute Zero
|
|
|
|
|
Iam having SQL Database MDF file how can i restore the database without LDF.Please anyone reply me and this is very very urgent
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
