Really basic query speed question..
(333 Views)
. In general, better to have 30 simple queries running to like 6 different tables, or 1 table which could return all results in one query
Purely for speed. It doesn't matter if it's less work for me that space is duplicated. What does SQL Server run faster
Oh yes, and the tables have about 800 rows.
|
Complex query
(294 Views)
Hi Friends!
I have this query which will fetch count of project_ids where proj_CONFIRMED=2 and COUNTRY_ID=6. This is working abs fine. Now I need to add one more to this query. Here is my tables structure.
select count(DISTINCT Pd.Project_id) from project_master PM,PROJ_DETAILS PD where PM.mask=0 and PM.PROJECT_ID=PD.PROJECT_ID and PD.COUNTRY_ID=6 AND PM.proj_CONFIRMED=2
Here is my table design.
PROJECT_MASTER(PROJECT_ID,PROJECT_TITLE,PROJ_CONFI RMED,MASK,REGION_ID,USER_NAME)
PROJ_DETAILS(PROJECT_ID,PROJ_DETAIL,COUNTRY_ID)
COUNTRY_MASTER(COUNTRY_ID,COUNTRY_NAME,REGION_ID)
REGION_MASTER(REGION_ID,REGION_NAME)
USER_MASTER(USER_ID,USER_NAME,COUNTRY_ID)
Apart from country_id=6 I need to see if (PM.USER_NAME'S COUNTRY_ID=6 WHEN REGION_ID=4)
Please Help
|
INNER JOIN vs Correlated Subquery !
(264 Views)
Hi Pals,
Need some help in understanding the internal execution of the below 2 queries.
Theoritically and upto my knowledge , i know Co-related sub-queries takes more time to execute.
Can anybody explain the reason for this.
Is there any difference in comparing/joining the two tables.
-- Using INNER JOIN
select e.ename, d.dname from Emp e INNER JOIN Dept d ON e.deptno = d.deptno
-- Using Correlated Sub-query
select e.ename,
(select dname from dept where deptno=e.deptno)
from Emp e
-- Sample Tables
create table Dept
( deptno int primary key,
dname varchar(20)
)
insert into Dept values (10,'Accounts')
insert into Dept values(20,'Sales')
insert into Dept Values(30,'Marketing')
insert into Dept values(40,'Production')
create table Emp
(Empno int primary key,
Ename varchar(20),
deptno int
)
insert into Emp
select 101,'Manu',10
union all
select 102,'Anu',40
union all
select 103,'Phani',30
union all
select 104,'Sharath',20
Please suggest.
|
Sp vs query?
(214 Views)
I have a general question, wondered if anyone knew what be the most effect way of getting data returned from my MSSQL database from my asp pages.
A stored proccedure or using a query string with a connection from the asp page
Basically I need to pull info from our site and I wondered what is the quickest way as my server is struggling as it's on a shared server.
|
Sql query
(496 Views)
how do I copy data from one table to another and after knowing that the copy is successfull I can delete the first table.
So dow go i make sure that my copy was successfull and i can delete my previous table.
pls let me know asap.
|
Crystal reports query question-
(547 Views)
hi,
Im modifying a query for a Crystal Report. The query is for a report. I have 7 fields ,all boolean and the user can pick one or more. It seems that most of the time, only one is selected.
I need to add these to the report. Based on the selections they make, I then put the corresponding text value associated with the boolean. I put separate CASE statements in my sql , but Im not going to have room to put those fields in the report. I tried putting them vertically, but the report doesn't look good. Am I on the right track or way off I have the same thing for all 7 fields (below).
I managed to squeeze all 7 fields together, making them really small, but when the user has selected something other than the first field displaying on the form, the field appears to the right instead of where it should. How do I do this Not sure about how to do this
Another problem I am having is this: I make changes to the report, but when I bring the web page up and select the report, it shows the old report, not the new one. I can't build anything ( I didnt think I had to because the reort is the same ) because I don't have the code-behind - does that matter Can you suggest what I should do
|
Help with a SQL query
(249 Views)
Hi ,
I have 3 tables:
Batches
-BatchId
-BatchName
-QueueId (current queue status for this batch)
...here BatchId is a PK.
Tasks
-TaskId
-BatchId
-QueueId
...here TaskId is a PK, and BatchId is a FK.
This table has all the status (QueueId) a specific batch goes through (scanning, recognition, export), and the task that handles it.
Queue
-QueueId
-QueueName
...here QueueId is a PK.
This table list the different states a batch goes through (scanning, recognition, export)
I am trying to create a report to list all batches, so I need to combine Batches with Tasks.
The trouble (for me) is that I am trying to pull QueueName from the QueueId in Batches, and Tasks, but I don't know how...
Here it is my query:
SELECT BATCHES.BATCHID, BATCHDEFS.BATCHDEFNAME, QUEUES.QUEUENAME, QUEUES.QUEUENAME AS [Tasks Queue Name]
FROM BATCHES INNER JOIN BATCHDEFS ON BATCHES.BATCHDEFID = BATCHDEFS.BATCHDEFID
INNER JOIN TASKS ON BATCHES.BATCHID = TASKS.BATCHID
INNER JOIN QUEUES ON BATCHES.QUEUEID = QUEUES.QUEUEID, TASKS
INNER JOIN QUEUE ON TASKS.QUEUEID = QUEUE.ID
I don't know how to use the QueueID for the Batches, and Tasks independently to retrieve the respective QueueName.
Could you help
|
How to optimize the sql query
(323 Views)
Hi
Is there any tool or some other way which can help in optimizing the sql queries so that the latency time can be reduced. I am using SQL Server 2000. Please give your suggestions.
|
Creating query against two tables
(283 Views)
stepdefinition has
steptype
flowid
stepid
task has
taskid
flowid
stepid
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
here's a visual
|
query problem
(285 Views)
hi.....
i m getting error when run this query..can we form such query
strSQL = "SELECT distinct y.company_id,y.company_name,z.location_id,z.locati on FROM appointment_detail AS x INNER JOIN company AS y ON x.company_id = y.company_id AND appointment_detail AS x INNER JOIN location_table AS Z ON x.location_id=z.location_id ORDER BY y.company_name,z.location"
error////////////
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''.
/sd/jobsearchasp2.asp, line 18
can any suugestion....................thanx
|
Please help with Update query
(317 Views)
Hi !
Please help...
How I can write a query in which I update some text in column.
I have a column with long text(not only one word),in this text I only want to update x to y ,for example.
|
Select query
(272 Views)
does any one no how to using sql
select * from table where id=x
and then wanna select the next 5 rows from the record x
|
query returning items that don't exist in another table
(285 Views)
Hi I am really struggling.
I want a query to return items from a main table that don't exist in another table.
I have two tables. One is a navigation table and one is a table with html associating to the navigation.
In an admin panel I want to be able to choose any navigation that has not yet had the html added to it.
eg this
SELECT * FROM bazaweb.navigation INNER JOIN bazaweb.pages on (bazaweb.navigation.nav_id = bazaweb.pages.nav_id AND bazaweb.pages.pages_type'special' AND bazaweb.navigation.lang_id=bazaweb.pages.lang_id)
returns the results which have html attribute.
I want the opposite to!.
(one is for edit & one is for add)
I've tried non equal () but that gives me hundreds of rows.....
Any ideas, have searched the web to no avail. Pehaps I need toc oem at this a different way.
|
Sql query?
(206 Views)
how do i do this
if i select from a table when a record has a null value it will display "0"
|
Help building query.
(214 Views)
Alright,
Not sure how to go about this.
I have a table that lists "Reason Codes" for Tickets.
Each Ticket may have multiple entries in the table, as they may have more than 1 Reason Code.
Each Ticket is supossed to have one of the reason codes labeled as "PRIMARY"
How do I create a query that checks for Tickets that have none of the Reason Codes marked as primary
OR if there is more than 1 CauseCode Marked as Primary for 1 ticket.
Table Columns are:
[CauseID] [MAIN_TICKET_ID] [CauseCode] [PrimaryCode]
1 1111 4 0
2 1111 3 1
3 1111 9 0
4 1112 2 0
5 1112 5 0
6 1113 2 1
7 1113 7 1
Example above.
I would want the no Primary Cause Code query to return just 1112
And the multiple Primary Cause Code per Ticket query to return just 1113
|
Query Help - Combining 4 tables into 1 query
(344 Views)
again,
I have 4 tables.
First table has TicketID and 3 addtional columns for cause IDs
like
MTTR table
Ticket ID1 ID2 ID3
AB345 1 4 2
Then I have 3 more tables. Each Table containing a ID and the Corresponding text
LIKE
Cause1 table
ID TEXT
1 Process
2 Troubleshooting
3 Customer Related
ect, ect, ect
Cause2 table
ID TEXT
1 Reduced Ticket Focus
2 Monitor Ticket Not Used
ect,ect,ect
Cause3 table
ID TEXT
1 Outside of Local Repair
2 Ticket not resolved when issues repaired
I need the resulting query to basically Contain
TICKET ID1 ID1text ID2 ID2text ID3 ID3text
is this possible
|
Help with sorting in a query
(252 Views)
,
I'm using the following query to list a top 20 keywords (most used) in a table. I'm trying but I can't sort by keyword field.
There is someone who could help me with this
select top 20
keywords,
count(keywords) as keywords_total
from (select
date_ins,
keywords
from table_name) as a
where
date_ins > dateadd(day,-60,getdate()) and
keywords ''
group by keywords
order by count(keywords) desc
|
Problem in writing a specific sql query
(202 Views)
I am facing an issue in writing a sql query.
DECLARE @QUERY varchar(8000),@s as varchar
SELECT @Query =
' SELECT CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
exec (@query)
In this query, I am converting a hexadecimal value to Int. My problem is in a single select statement I want few mpre fields (eg:ww8_mtr_no, Int datatype) to be extracted.
I tried writing it like this:
DECLARE @QUERY varchar(8000),@s as varchar
SELECT @Query =
' SELECT ww8_mtr_no,CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
exec (@query)
Error: Invalid column name 'ww8_mtr_no'.
Then I tried:
DECLARE @QUERY varchar(8000),@s as varchar
SELECT @Query =
' SELECT' + ww8_mtr_no+',CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
exec (@query)
error: Syntax error converting the varchar value ' SELECT ' to a column of data type int.
Can anyone plz suggest how to write it.
|
Need help to write the query
(272 Views)
I need help to write a query. The details of which is given below
employee table
Empid EmpName EcatID ProjectID
1200 Rama 5 2
1201 Shiva 5 2
1202 arab 6 44
1207 Kumar 3 28
1208 Despande 6 2
1210 Divya 3 50
1230 praveen 1 44
1232 Raghu 1 28 1247 sumitra 1 2
1266 Manoj 1 39
1274 Siddu 2 39
Empskill Table
EmpID CourseID SelfRating Evalrating
1201 10 4 -
1202 11 8 7
1202 100 8 -
1200 12 7 7
1207 99 10 9
1207 100 10 8
1207 101 8 8
1207 102 5 5
1207 103 6 4
1207 104 4 4
1274 104 8 8
Result Needed:
Empid PMName No Of Subordinates TotalNoofSubjsratedbysubords TotalNoOfEvalRating
1230 praveen 1 2 1
1232 Raghu 1 6 6
1247 sumitra 2 2 1
1266 Manoj 1 1 1
EcatID =1 means he is a Project Manager
a PM has a ProjectID, his sub-ordinates have the same ProjectID (thats how you locate them)
|
Concatenate records from a sub-query together.
(294 Views)
Hi ,
I'm building a website for a floor-tiles company and Im currently working on the online sample request section.
I have a table called tblRequest and another table called tblSample. tblSample stores a list of information on all of the tiles available and tblRequest contains info on each request made by a customer. I have a third table calledtblSampleRequest which basically acts as the middle man for the many-to-many relationship between tblRequest and tblSample...
tblRequest -> tblSampleRequest
|
|
