|
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
|
|
|
|
|
|
hi,
I need select query to select number of records(1 to 50 then 51 to 100 from 500 rows)from n rows of selection. not from id I think row id.
thanks in advance.
Have A Nice Day!
Murali.M
Blog
|
|
|
|
|
You mean like SELECT TOP 50 ... ?
|
|
|
|
|
no any 50 records.
Have A Nice Day!
Murali.M
Blog
|
|
|
|
|
|
Hi dear friends
I have a database (sql server) that every username should Enter some information in system & his (Admin,Admin User )then his (Admin) should give those Update Information to his (Users).
Now, I have some Problems to solve this & need your help
1) This program offline & there are no network!
2) The (Users) shouldn’t have all database & they have just information about themselves
This like organization chart that each user can just access to his information.
I want to know, how can I do backup & restore this information? In which way?
Do you Know any software that can Restore?
Backup this information the best possible way?
Image of this Message[]
|
|
|
|
|
This is not a backup/restore issue. You need to do some research into replication and distributed database strategies. This is not a trivial subject.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello there guys,
i trying to perform some right join to keep all records from tbl custumers, it works fine but when i do the "where" condition i noticed it killed my right join
how can i solve this?
i have a way to get the output i want but is very messy, so i guessed u guys could help me here, i sure is very easy 
select Customers.cname as c_id, sum(Products.price*Purchases.quantity) as z
from Purchases
right outer join Customers on (Customers.CID = Purchases.cust)
left outer join Stores on (Stores.SID = Purchases.Stores)
left outer join Products on (Products.SKU = Purchases.prod)
###where Stores.city = "Porto"
group by Customers.cname
order by z desc
(my aim is to show total value of purchaces per client in some store, but i need to include all client even those that didn´t shop (zero values))
thanks
nelsonpaixao @yahoo.com.br
trying to help & get help
modified 7-Dec-13 15:11pm.
|
|
|
|
|
Caveat I'm a SQL Server user but would assume the syntax is similar.
Put the where clause into the join
left outer join Stores on (Stores.SID = Purchases.Stores and Stores.City = 'Porto')
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
