|
Change the INNER JOIN to a LEFT JOIN.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Great thanks for that. Iv'e neaver really fully understood the other JOIN methods. What is the exact reason for using LEFT JOIN? Strictly for this reason?
Thanks
|
|
|
|
|
It is. Basically they work like this (assuming that you have two tables A and B):
SELECT * FROM A
INNER JOIN B
ON A.ID = B.FKID This returns records where there is a match in both A and B.
SELECT * FROM A
LEFT JOIN B
ON A.ID = B.FKID This returns ALL records from A regardless of whether or not there is a matching record in B. Similarly, RIGHT JOIN returns all records from B regardless as to whether or not there is a matching entry in A.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Oh i see. That makes alot of sense i guess. Thanks for that
|
|
|
|
|
|
I should have realised that you would have a summary somewhere. Nice one.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Am now Searching certain range in a character string Attrib_Code,
which has more Characters Example 019600197/1/R.PV000000. i have more than 50 thousand recards with the same First Four numbers. they differ from "00197", the other parts are not important for me. i want to search a range from "000197" till "000274", how do i search the rages in strng? inside substring.?
Vuyiswa
|
|
|
|
|
try this
SELECT * FROM tblA
WHERE cast(substring(Attrib_Code, 5, 5) as bigint) between 197 and 274
Regards
KP
|
|
|
|
|
select * from property
where Cast(Substring(Attrib_code,5,5) as bigint)
between 00274 And 01189 Man the Code works thanks man, i forgort to ask you something again, the is a Column , i need to Extract those who are in Extension 6, i tried extent the query but i get a lot of errors, your experience will help.
Thanks man
Vuyiswa
|
|
|
|
|
not getting.
Can you please explain in detail.
Regards
KP
|
|
|
|
|
Hi All,
I have written a stored procedure to execute common queries where we need all data from a table or with some clause, like this
CREATE PROCEDURE dbo.GetAll
(
@SQLQuery nvarchar(max)
)
AS
SET NOCOUNT ON
EXEC sp_executesql @SQLQuery
THIS PROECDURE WILL ACCEPT QUERY WITH WHERE CLAUSE ETC, Please suggest me if this one is good solution using stored proc, as you can see i used sp_executesql to execute query for caching query plan etc,,, suggest better one .. or if this is fine please let me know.
Many thanks,
adnan
Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com
|
|
|
|
|
I really don't know where to start here. This type of dynamic SQL is really really bad because it represents a huge gaping security flaw in the application. Imagine if a query managed to get through with the following in:
DELETE sysobjects; You can probably see how this is bad. I suggest that you read the following article by Colin; http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp[^]
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Wow! This is a SQL Injection Attackers heaven.
|
|
|
|
|
Beyond the obvious security problems and general 'why'-ness of this code, why would you even bother wrapping a call to one procedure in another procedure that takes the same parameters....??
adnanrafiq wrote: caching query plan
But the select (and therefore the query plan) are going to change each time...
This makes my head hurt...
|
|
|
|
|
Thanks for Repley and for correcting me.
But the select (and therefore the query plan) are going to change each time...
Yes you are write, but there is caching of query plan, if i give the select statement in stored procedure string variable and pass only the where clause paramerter values but in this case i am doing this beucase in my search criteria user where clause vary on its selection..
What query you guys will suggest in this case:
Skill table has following fields: SkillName , SkillExperience
All skills are listed in listbox and user wants to search those profiles who have 2 years experience in ASP.Net and 3 years in Java, ......
http://msdn2.microsoft.com/en-us/library/ms188001.aspx[^]
And One more thing, if i do validation on server side to prevent the maliciaous attacks, as suggested in the Colin article, like filtering sql query only for select statment.
Then does it do work or still its top bad solution.
Sorry in Advance for bad English.
Many Thanks,
adnan
Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com
|
|
|
|
|
Hi All,
I have created a DTS in SQL-server 2000 by using design wozard. It has transformation for importing data from a ".csv" file into a SQL-server database table. Its doing well.
Now I would like to change the source and destination od this DTS, how can I reset the source and destination connection properties at runtime.
Pls. help me, give me code snippet if possible.
Thank you,
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
I have the duplicate data in the table how could i retrieve them. ex: the name field is like
1. xyz
2. abc
3. xyz
4. pqr
5. abc
6. ijk
7. mno
here I want only the duplicate records. What is the query?
Thanx in advance.
yamini
|
|
|
|
|
select name FROM tblA GROUP BY name HAVING count(name) > 1
Regards
KP
|
|
|
|
|
|
|
Select name from tblname groupby name having count(name) > 1
Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com
|
|
|
|
|
|
Are you doing a job interview where they've given you access to the internet? You seem to be asking interview questions all over the place. If you don't know the answers to the basic questions that you've been asking, then I would suggest that a career in IT is probably not the best option for you.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi all,
i have a darabase in access i wanna edit a column in a table. my coding has no errors. also the dataset gets updated with the change but the real table doesn't get updated .
this is my coding.
OleDbConnection OLEDBcon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\WmsFoodCityPerformance.mdb" +
";Jet OLEDB:Database Password=sanuja");
OLEDBcon.Open();
string sel = "SELECT * FROM CON_AKU";
OleDbDataAdapter OLEDBda = new OleDbDataAdapter();
try
{
OleDbCommand OLEDBselcom = new OleDbCommand(sel, OLEDBcon);
OLEDBda.SelectCommand = OLEDBselcom;
}
catch
{
}
finally
{
DataSet ds = new DataSet();
OLEDBda.Fill(ds);
DataTable dt = new DataTable();
dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
dr.BeginEdit();
dr["BGT_JUN"] = 0;
dr.EndEdit();
dr.AcceptChanges();
dt.AcceptChanges();
}
OLEDBda.Update(ds);
MessageBox.Show("Done");
dataGridView1.DataSource = ds.Tables[0];
}
}
please help
Regards
Ruwandi
rkherath
rkherath
|
|
|
|
|
Hello all:
Using DataGridView connecting to SQL2005 database. I was able to view the dataset on one of the table that I selected.
I would like to use BindingNavigator to save or add a row onto this particular table.
Could someone please give me an example of how i'm going about to do this?
Thanks in advance,
Eyungwah
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
