|
I'm trying to search some data in a table I don't have control over and I need to figure out how to do an logical and between two specified search items.
I need to find all rows in a table that has the same value in collumn A and column B is equal to X or Y.
The is similar to the following, but it contains more than 600,000 records.
Tablename: data_table | id | record_id | parm_type | value | | 0 | 0 | 1001 | 10 | | 1 | 0 | 1002 | 20 | | 2 | 0 | 1003 | 30 | | 3 | 0 | 1004 | 40 | | 4 | 1 | 1001 | 15 | | 5 | 1 | 1002 | 25 | | 6 | 1 | 1003 | 35 | | 7 | 1 | 1004 | 45 | | 8 | 2 | 1005 | 20 | | 9 | 2 | 1002 | 30 | | 10 | 2 | 1003 | 40 | | 11 | 2 | 1008 | 50 | | 12 | 3 | 1007 | 20 | | 13 | 3 | 1002 | 30 | | 14 | 3 | 1003 | 40 | | 15 | 3 | 1007 | 50 | | 16 | 4 | 1001 | 20 | | 17 | 4 | 1002 | 30 | | 18 | 4 | 1007 | 40 | | 19 | 4 | 1008 | 50 |
I want to get every value with the same record_id, that has a parm_type type of 1002 or 1003 and the value is greater than 20 for type 1002 and less than 40 for 1003.
Here is what I tried but it was taking very long and gets execution timeouts
SELECT id,record_id,parm_type,value FROM data_table
WHERE record_id IN
(
SELECT record_id FROM data_table
WHERE parm_type=1002 AND value > 20
)
AND record_id IN
(
SELECT record_id FROM data_table
WHERE parm_type=1003 AND value > 40
);
I know there have to be others ways to accomplish this, but I'm no DBA...
|
|
|
|
|
Couple of ideas,
1 - Create a temporary table. Execute each query separately and insert to temporary table. Finally, select the temporary table. Here is it
CREATE TABLE #temp
(
);
insert into #temp
SELECT record_id FROM data_table WHERE parm_type = 1002 AND value > 20
insert into #temp
SELECT record_id FROM data_table WHERE parm_type=1003 AND value > 40
SELECT * FROM #temp
2 - Use UNION[^]
SELECT record_id FROM data_table WHERE parm_type = 1002 AND value > 20
UNION
SELECT record_id FROM data_table WHERE parm_type=1003 AND value > 40 
|
|
|
|
|
Navaneeth thank you for your reply.
I think a UNION statment would merge record_id that would match either of the parm_type requirements.
Please correct me if I am wrong!
|
|
|
|
|
SELECT id,record_id,parm_type,value
FROM data_table
WHERE (parm_type=1002 AND value > 20)
OR (parm_type=1003 AND value > 40);
|
|
|
|
|
Thank you for the reply i.j.russell
This is acually what I have currently, but this is a logical OR where I'm needing to do a logical AND. I want to a one or more record_id values that contains a "parm_type=1002 AND value > 20" and a "parm_type=1004 AND value > 40."
This makes it a bit harder to search 
|
|
|
|
|
Maybe I need more clairification (and a better example)
Ok so I want to find everything with the same record ID, that has atleast one match for every parm_type and value spesified.
What has be suggested so far is a "logical" OR and meaning any of the parm_type and value criteria match.
A better example would be one that actually returns data, I apologize for my previous bad example.
SELECT id,record_id,parm_type,value FROM data_table
WHERE record_id IN
(
SELECT record_id FROM data_table
WHERE parm_type=1002 AND value > 20
)
AND record_id IN
(
SELECT record_id FROM data_table
WHERE parm_type=1003 AND value >= 40
);
If I were to run this with the data_table in my previous post I'd expect to be returned would be the following result table:
Result from: data_table| id | record_id | parm_type | value |
|---|
| 8 | 2 | 1005 | 20 | | 9 | 2 | 1002 | 30 | | 10 | 2 | 1003 | 40 | | 11 | 2 | 1008 | 50 | | 12 | 3 | 1007 | 20 | | 13 | 3 | 1002 | 30 | | 14 | 3 | 1003 | 40 | | 15 | 3 | 1007 | 50 |
I hope this could clear things up.
|
|
|
|
|
I'm a bit unclear what you're looking for here. What do you mean by "every value with the same record_id"?
The solutions posted by the others will give you a list of record_ids that have entries in the table for either (type = 1002 and value > 20) OR (type = 1003 and value > 40).
The solution that you posted would give you a list of record_ids that have entries in the table for both (type = 1002 and value > 20) AND (type = 1003 and value > 40).
These are not the same thing, so which one are you looking for?
In terms of your original question, this is probably no help since you do not have control over the table, but one obvious question is: is there an index over parm_type? Without an index over this column, the DBMS is going to end up doing a full table scan which will take a long time on a big table (i.e. it will simply look at every row in the table in turn to see if it matches the query).
|
|
|
|
|
David Skelly wrote: The solution that you posted would give you a list of record_ids that have entries in the table for both (type = 1002 and value > 20) AND (type = 1003 and value > 40).
Is exactly what I'm trying todo, but need a faster method.
I don't really have control over the table, but I can force an index on that column since I can run SQL against the whole table. Maybe it'll speed up if I toss an alter table to add an index to the result_id...
Yes, yes that just might work! David Skelly you're an evil genius.
|
|
|
|
|
I added an index to the record_id indicator with an alter table and now my original query returns in 0.58 seconds (db time) instead of ~400 seconds (db time)
Guess this just goes shows me even more how crappy the self proclaimed coder/dba that designed this was, not that I didn't already know he was full of himself by looking at his code 
|
|
|
|
|
This does not seem to work; I dont know which table the value is in.
USE DB
Go
SELECT *
FROM sys.Tables where fieldid = '%'
GO
I presume I cannot use a conditional clause in that statement?
It will parse but errors out
Regards,
Hulicat
|
|
|
|
|
This is what I would use ...
select SO.NAME AS TABLENAME, SC.NAME AS COLNAME
from sysobjects so, syscolumns sc
where so.id = sc.id and sc.name = 'DESCRIPTION'
This query will give you a listing of every table that has a field that is named, 'DESCRIPTION'.
Good luck,
david
|
|
|
|
|
|
Hi, I need help with gridview?
actually the gridview is supposed to show a view(aka join) consisting of two tables .
In my case I have (userid) , (money deposited) as fields in table 1; userid can also be left as null.
I have another table I have first_name, last_name,phone numbers( fields) in table2
I want to display first_ name ,money deposited.
in the gridview.
It has to show all the enteries made in table 1 either it is null or has some userid; if it becomes null , then a space should appear instead of displaying null.
can anyone please guide me how to do this?
thanks
|
|
|
|
|
try something like this - I have to assume that userid appears in table3 also, therwise this is no way to join them
select isnull(first_name,' '), [money deposited]
from table1 t1
left outer join table2 t2 on t2.userid = t1.userid
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
how to check "is not null condition" in a field of single select query
|
|
|
|
|
select *
from table
where column is not null
Wout Louwers
|
|
|
|
|
Thanks for your reply.
but i didn't mention like this.
There is any possibilities to check the field as not null that which is to be selected using select query (not in where condition)
|
|
|
|
|
You can use select IsNull(Column, 'Value') from table. But I am not sure if this is what you mean.
Wout Louwers
|
|
|
|
|
select isnull(column,'N/A') from tablename where column='condition'
Hope this will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi,
kindly let me know, How may I create cold backup and restore cold backup using Oracle 10g ?
Thank you
(Riaz)
|
|
|
|
|
I had not heard of a cold backup before but there is plenty of resource[^] out there pertaining to this subject.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I get the following errors when starting my local SQL server:
2009-08-05 11:46:04.68 Server Error: 17182, Severity: 16, State: 1.
2009-08-05 11:46:04.68 Server TDSSNIClient initialization failed with error
0x2, status code 0xd.
2009-08-05 11:46:04.68 spid8s Starting up database 'model'.
2009-08-05 11:46:04.68 Server Error: 17182, Severity: 16, State: 1.
2009-08-05 11:46:04.68 Server TDSSNIClient initialization failed with error
0x2, status code 0x1.
2009-08-05 11:46:04.69 spid5s Starting up database 'msdb'.
2009-08-05 11:46:04.69 Server Error: 17826, Severity: 18, State: 3.
2009-08-05 11:46:04.69 Server Could not start the network library because o
f an internal error in the network library. To determine the cause, review the e
rrors immediately preceding this one in the error log.
2009-08-05 11:46:04.69 Server Error: 17120, Severity: 16, State: 1.
2009-08-05 11:46:04.69 Server SQL Server could not spawn FRunCM thread. Che
ck the SQL Server error log and the Windows event logs for information about pos
sible related problems.
I have looked at a few solutions to the errors such as disabling the VIA protocol in the configuration manager, but it was already disabled. I did discover that the issue seems to be with the TCP/IP protocol, which I can't even enable. I found this page:
http://www.thelastpickle.com/2008/02/28/invalid-server-tcpip-settings/[^]
unfortunately the properties of the TCP/IP protocol are totally missing the “Listen All” property. The only property under TCP/IP is "keep alive". Enabling a single IP doesn't help either.
Any recommendations?
modified 4-Apr-21 21:01pm.
|
|
|
|
|
If it was working before and isn't working now, I would look to differences.
Maybe a windows update or other software installed. Maybe windows update is on automatic and you don't know it.
Try rolling any changes back to when things were working.
If it never worked, and this is a "fresh" install, go ahead and remove and reinstall.
Byron Mann
mannb@hostmysite.com
byronosity@gmail.com
Software Architect
hosting.com | hostmysite.com
http://www.hostmysite.com/?utm_source=bb
|
|
|
|
|
I had the same problem today.
Found out that the error 0x2 = The specified service does not exist as an installed service.
sc query 2
Only thing that I was able to do was to uninstall the database service then reinstall it.
|
|
|
|
|
For my project, I have setup a small domain, and arranged a LAN connection.
From my domain, when I connect with my Sqlserver 2008 remotely It's connected, no problem.And the application is running good.
But when I run the program, out of domain not in LAN, it's not connecting with my sql server and showing the error,
"A network-related or instance-specific error occurred while establishing a connection to sql server......(provider; Named pipes provider, error: 40- could not open a connection to sql server).
Iam using the followings as connection string
Dim ConStr As String = "Data Source=1.1.1.1; initial Catalog=Employee_Master; User Id=Codeproject; Password=xxxxxxxx"
It's cnnecting under LAN, but not from remote machines.
Any ideas to fix this problem ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
