|
When writing a query, I like to be explicit about what is intended to do.
If I want only records with a match, they I say 'INNER JOIN'.
If I want partial matches, I say 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN' based on how I feel like writing the query, but it is usually 'RIGHT OUTER JOIN'.
Tim
|
|
|
|
|
Why cant we use left join instead of left outer join??
|
|
|
|
|
I did not write the design for whatever version of SQL is in use.
My personal preference it to be explicit in what I am doing.
If the version supports 'LEFT JOIN' and 'LEFT OUTER JOIN' and treats them the same, then use either one.
Some SQL instances use different syntax; I just like the use to be obvious to the reader.
|
|
|
|
|
|
Left Join and Left Outer Join is the same thing. It's just a matter of verbosity in the syntax
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
ok . thanks for your reply.
|
|
|
|
|
|
Hi,
i'm implementing that using the method exposed in VB DataGridView Automatically Saving Updates to a Bound Database[^] but
how to handle exception thrown when DataAdapter.Update is called in RowValidated?
I can catch the exception with a Try but how to cancel the move to a new row and to force user to correct the edited row?
What i would obtain is a "SQL Server Management Studio-style". When you edit a row and move to another, if an exception is detected (duplicate key for example), a message is shown, the edit glyph is mantained in the edited row and cursor remain on the edited row.
Thanks
|
|
|
|
|
Before it's validated, it will be validating[^].
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Does anybody know if there is a performance difference between these two example queries:
SELECT * FROM tblFox WHERE stringFieldValue IN ('Ring-ding-ding-ding-dingeringeding!', 'Wa-pa-pa-pa-pa-pa-pow!')
SELECT * FROM tblFox WHERE stringFieldValue='Ring-ding-ding-ding-dingeringeding!' OR stringFieldValue= 'Wa-pa-pa-pa-pa-pa-pow!'
????
I would Google it if I knew what to enter to get a relevant answer...
Anything that is unrelated to elephants is irrelephant Anonymous ----- The problem with quotes on the internet is that you can never tell if they're genuine Winston Churchill, 1944 ----- I'd just like a chance to prove that money can't make me happy. Me, all the time
|
|
|
|
|
I did not know exactly that there is performance difference. But query using IN will reduce your typing and looks good. If there are few more data to search in OR condition, You type field name again and again??
|
|
|
|
|
Depends on what database you're using and what indexes you have in place, but normally the query optimizer would realize that they are the same thing.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
Johnny J. wrote: Does anybody know if there is a performance difference between these two example queries: Not so much difference I think. But I prefer IN instead of OR in this case. Using IN you could write cleaner query. What would you do if there're 10+ OR conditions in your query?
Johnny J. wrote: I would Google it if I knew what to enter to get a relevant answer... I tried http://www.google.com/search?q=SQL+IN+vs+OR[^] & got this one
SQL Speed Test: IN vs OR[^]
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
If you are using SQL Server, you could use "Show Execution Plan" and see if there is a difference.
If you are using Oracle, I believe there is a 1000 element limit to the "IN" clause. At a previous company I worked for we got bit by this. (This was a while ago, maybe Oracle 9, it may not be an issue now)
|
|
|
|
|
Oracle still has this limit (in older versions it was 256 elements)
The purpose with the limit is that parsing the query is quite resource demanding and if you're having more than 1000 elements in an in statement they're claiming that you're doing something wrong anyway.
Use an array instead.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
Based on the full text entered by user in "search" textbox , i want to search every words that is present in various columns of my tbl_products table. how to achieve this.i prefer to use contains than like.plz help me. Examples are welcomed than links plz help
|
|
|
|
|
Contains is a c# or .net concept, for sql you need to use wild cards % in your stored procedure.
Set @SearchText = '%' + @SearchText + '%'
Where Col1 like @SearchText
or Col2 like @SearchText
or Col2 like @SearchText
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
rakeshjena wrote: Examples are welcomed than links It's not something that can be explained with a copy/paste example. If you want a full-text search, then you have to configure[^] the server.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Based on the full text entered by user in "search" textbox , i want to search every words that is present in various columns of my tbl_products table. how to achieve this.i prefer to use contains than like.plz help me. Examples are welcomed than links plz help
|
|
|
|
|
What is the use of TempTables in SQL?? Is it a preferred one to use temptables??
|
|
|
|
|
|
|
SO I have two tables - one of "tasks" and another to log the time they are carried out each day - viz:
table "tasks"
ID - autoincrement PK
sJob - varchar task name
table "tasks_log"
ID - autoincrement PK
JobID - mapped to tasks.ID
dDateTime - date and time task is performed
Now, what I want to do is produce in one SQL statement a result set in which each row consists of the task (sJob), followed by the time it was carried out on each day within a given range
So... the result set will have variable columns depending on the date range, and the SQL sdtatement will need to be built dynamically.
A (probably not the best!) partial solution is as follows
Suppose we want the results for three days, Dec 1 to Dec 3
SELECT T.sJob, L1.dDateTime AS Dec_1, L2.dDateTime AS Dec_2, L2.dDateTime AS Dec_3
FROM tasks AS T
LEFT JOIN tasks_log AS L1 ON L1.JobID = T.ID
LEFT JOIN tasks_log AS L2 ON L2.JobID = T.ID
LEFT JOIN tasks_log AS L3 ON L3.JobID = T.ID
WHERE
DATE(L1.dDate) = '2013-12-01'
AND DATE(L2.dDate) = '2013-12-02'
AND DATE(L3.dDate) = '2013-12-03'
ORDER BY T.sJob
One obvious drawback of this is that it will be limited to a relatively small date range, but worse is that there is something I haven't told you! That is, that not all the tasks are carried out on all days.
Given this, the result set will only consist of those tasks that were, in fact, carried out on each day within the given date range.
What we need, if it's not asking the impossible, is to list them all, with NULLs showing on days any particular task was not performed.
Obviously there are other approaches, but it wuild be nice if there was simple (...) SQL statement that could do it all in one go...
|
|
|
|
|
What you need is a pivot query. This article [^]may help.
The ides is to prepare your data before pivoting. Create a table with the dates you want to report, and in your pivot join those date to the data table and then pivot the result on the dates.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|