SQL Query


I have a column for availability in the Options table.

This column contains 2 records

1st record: "Monday, Tuesday, morning"
2nd record: "Monday, evening".

How do i write a query that returns"

1st record: if "avail = morning"
both records: if "avail = monday"
both records: if "avail = monday, morning"
Posted On: Tuesday 20th of November 2012 02:56:38 AM Total Views:  322
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Really basic query speed question..

. 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.
VIEWS ON THIS POST

268

Posted on:

Tuesday 20th November 2012
View Replies!

Complex query

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
VIEWS ON THIS POST

229

Posted on:

Tuesday 20th November 2012
View Replies!

INNER JOIN vs Correlated Subquery !

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.
VIEWS ON THIS POST

205

Posted on:

Tuesday 20th November 2012
View Replies!

Sp vs query?

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.
VIEWS ON THIS POST

164

Posted on:

Tuesday 20th November 2012
View Replies!

Sql query

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.
VIEWS ON THIS POST

423

Posted on:

Tuesday 20th November 2012
View Replies!

Crystal reports query question-

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\t 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
VIEWS ON THIS POST

467

Posted on:

Tuesday 20th November 2012
View Replies!

Help with a SQL query

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
VIEWS ON THIS POST

201

Posted on:

Tuesday 20th November 2012
View Replies!

How to optimize the sql query

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.
VIEWS ON THIS POST

269

Posted on:

Tuesday 20th November 2012
View Replies!

Creating query against two tables

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
VIEWS ON THIS POST

226

Posted on:

Tuesday 20th November 2012
View Replies!

query problem

hi..... i m getting error when run this query..can we form such query\t\t\t 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
VIEWS ON THIS POST

240

Posted on:

Tuesday 20th November 2012
View Replies!

Please help with Update query

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.
VIEWS ON THIS POST

247

Posted on:

Tuesday 20th November 2012
View Replies!

Select query

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
VIEWS ON THIS POST

217

Posted on:

Tuesday 20th November 2012
View Replies!

query returning items that don't exist in another table

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.
VIEWS ON THIS POST

233

Posted on:

Tuesday 20th November 2012
View Replies!

Sql query?

how do i do this if i select from a table when a record has a null value it will display "0"
VIEWS ON THIS POST

169

Posted on:

Monday 26th November 2012
View Replies!

Help building query.

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
VIEWS ON THIS POST

177

Posted on:

Monday 26th November 2012
View Replies!

Query Help - Combining 4 tables into 1 query

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
VIEWS ON THIS POST

287

Posted on:

Monday 26th November 2012
View Replies!

Help with sorting in a query

, 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
VIEWS ON THIS POST

213

Posted on:

Monday 26th November 2012
View Replies!

Problem in writing a specific sql query

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.
VIEWS ON THIS POST

158

Posted on:

Monday 26th November 2012
View Replies!

Need help to write the query

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)
VIEWS ON THIS POST

211

Posted on:

Monday 26th November 2012
View Replies!

Concatenate records from a sub-query together.

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
VIEWS ON THIS POST

245

Posted on:

Monday 26th November 2012
View Replies!