|
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
|
|
|
|
|
hi ! having problem to use undedicated(local)server that's why i want to make own query analyzer using text box in asp.net. please help me.
example picture
|
|
|
|
|
I wonder just what your expectations are from this post?
You have not asked a question but simply stated a rather vague requirement to reproduce a reasonably complex application in ASP.net. What do you expect us to do to help?
Have you looked at existing offerings[^] and do you think you have something better to offer, or a cheaper alternative!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
in C# got error while executing update query. error is "syntax error in update statement".
my connection string is
strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
Directory.GetCurrentDirectory() + "\\album.accdb;Persist Security Info=False";
query statement
m_thisCommand = m_thisConnection.CreateCommand();
strTemp = "update users set password='admin' where username='admin'";
m_thisCommand.CommandText = strTemp;
m_thisCommand.ExecuteNonQuery();
here
m_thisCommand is OleDbCommand
m_thisConnection is OleDbConnection
Thanks in advance
Have A Nice Day!
Murali.M
Blog
modified 6-Dec-13 4:20am.
|
|
|
|
|
"Password" is a reserved keyword[^] and hence, not a good name for a column.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
i think u r missing a statement...
m_thisCommand.CommandType = CommandType.Text;
please try this,,,
DineshT
|
|
|
|
|
Commandtype.text is default and does not need to be specified.
Oh, and Txtspeak is considered immature and shouldn't be used in professional context.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
For the sake of this question, I've massively reduced everything. I know there are alternate solutions, but I want to understand SQL better and solve this specific problem. (So please refrain from suggesting alternate schemas--I already have those; I really want to understand INTERSECT vs SELF INNER JOIN.)
Given two tables with two columns each:
Table: Words
Id Name
1 One
2 Two
3 Three
Table: Letters
Id Letter
1 A
1 B
1 C
2 A
2 D
3 A
3 C
The task is to find all the Ids in Words which match two letters from Letters. For example, I want to find all the rows in Words which match the letters A AND C from letters. Again, massive oversimplification.
I can do the following (in SQLite):
SELECT DISTINCT Words.Name FROM Words JOIN Letters ON Words.Id=Letters.Id WHERE Letters.Letter='A'
INTERSECT
SELECT DISTINCT Words.Name FROM Words JOIN Letters ON Words.Id=Letters.Id WHERE Letters.Letter='C'
ORDER BY name COLLATE NOCASE
This returns One and Three. I'm happy. Except, it bugs me. I should be able to do the same thing with a series of INNER JOINS, but I've yet to come up with a solution which actually runs. Even with an intermediate table, I'm still not sure how to solve it and thus want to first solve it with this schema. Is there a SQL wizard in the house?
EDIT: Just after posting this, I came up with the following. It works, but is it optimal? Is there a better way? (It seems that if Words is really big, we end up with two huge joins which are then reduced or with the optimizer figure this out?)
SELECT DISTINCT Words.Name FROM Words
INNER JOIN Letters AS L1 ON Words.Id=L1.Id
INNER JOIN Letters AS L2 ON Words.Id=L2.Id
WHERE L1.Letter='A' AND L2.Letter='C'
ORDER BY Words.Name COLLATE NOCASE
I aslso got the following suggestion:
SELECT DISTINCT Words.Name FROM Words
INNER JOIN Letters AS L1 ON Words.Id=L1.Id AND L1.Letter='A'
INNER JOIN Letters AS L2 ON Words.Id=L2.Id AND L2.Letter='C'
ORDER BY Words.Name COLLATE NOCASE
|
|
|
|
|
Personally I use the self join, mainly because the intersect is a new fangled concept that has not penetrated my inertia. Some of the new stuff is just repackaging the the old stuff to be more palatable, linq to sql comes to mind.
My TSQL seems to have ossified about a decade ago as that gets 95% of my work done. I'd hate to have to set up a distributed database these days.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Use joins whenever it's possible. The DB will then be able to use indexes for the joining.
Intersect is meant to be used when you have two resultsets with the same columns but different origins and no common indexing.
Instead of a self join you can try this query:
SELECT W.name
FROM Words w JOIN Letters l ON w.ID = l.id
WHERE l.letter IN ('A','C')
GROUP BY W.name
HAVING Count(w.name) = 2A group by is generally faster than a distinct and it will never need more than one index seek, your self join might need two depending on what the optimizer comes up with. But it's a bit quirky as you need to have the letter count available.
But you should consider it if you have large resultsets because it can be pipelined and therefore need much less memory.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
