Brief history: I'm writing a stored procedure to support a legacy reporting system (using SQL Server Reporting Services 2000) on a legacy web application. In keeping with the original implementation style, each report has a dedicated stored procedure in the database that performs all the querying necessary to return a "final" dataset tha...
I have two tables: t1: f1, f2, f3, f4, rowid_t2, sts t2: f1, f2, f3, f4, sts with different amounts of records that exceeds 10 millions. I need to match them using f1, f2 and f3 of each table as the keys, the relation is that one record of t1 can match with one record in t2 or many records of t1 can match with one record in t2, the m...
If I have a table with a StartDate column and an EndDate column can I produce a query that returns a set including every day in the range. I could use a table variable and do some procedural code but I'd like to know if there's a way to do it in a query. E.g. StartDate = 1/1/2010, EndDate = 1/5/2010, result would be: 1/1/2010 1/2/201...
I am refactoring an old Oracle 10g schema to try to introduce some normalization. In one of the larger tables, there is a text field that has at most, 10-15 possible values. In my mind, it seems that this field is an example of unnecessary data duplication and should be extracted to a separate table. After examining the data, I cannot...
I have to create a table and store Active Directory SIDs representing an User or a Group. How would you name the category representing both an User and a Group ? Edit 1. Table will contain four columns : ID ( PK ), SID's Name, SID's value and another column for SID's Type ( 0 for User, 1 for Group ). Please suggest the table name...
Hi, I'm trying to write an SQL query for my program, but I just can't figure out how. I don't know enough SQL. I'm trying to implement an online team system (for some website). I have two tables: teams | teamId, eventId teammembers | teamId, userId, status Now, I need to: "delete all records in teammembers where the eventId for the ...
What I'm trying to do is this: select Store.Id, (select COUNT(*) from StoreProduct where StoreProduct.Store_id = Store.Id) as _count from Store where _count > 3 SQL Server says it's invalid because the column name '_count' is invalid. Why it's invalid if I'm declaring it? ...
i am trying to teach myself SQL and of course I would like to follow best practices. I have created two querys to find the latest record : select * from AppSurvey where DateLastUsed >= ( SELECT MAX(DateLastUsed) FROM AppSurvey) and select top 1 * from AppSurvey order by DateLastUsed desc is one of these methods more efficent tha...
I was wondering why can't I use alias in a count(*) and reference it in the having clause. For instance: select Store_id as StoreId, count(*) as _count from StoreProduct group by Store_id having _count > 0 Wouldn't work.. But it works if I remove _count and use count(*) instead. ...
Here is my table and data of these tables Table name: Code CID Code 1 abc 2 def 3 xyz Table Name : Details ID Name CID 1 a 1 2 b 2 Resultant Table: ID Code Name 1 abc a 2 abc Null 3 def b 4 def Null 5 xyz Null 6 xyz Null I nned to ...
Hello! I want that when I execute a query for example DELETE FROM Contact, and an error is raised during the transaction it should delete the rows that are able to be deleted raising all the relevant errors for the rows that cannot be deleted. ...
create table A (id, col_A, col_B, col_C) id = unique id for every row being persisted either col_A or col_B will have a valid value, but both columns will not have a value for each persisted row at the same time. e.g. insert into A (id, col_A, col_C) values (1, "a", "c") insert into A (id, col_B, col_C) values (1, "b", "c") insert i...
Hi, i have two columns that store values(numbers), how do i select where my given number is between the values in the two columns? Example `id | col1 | col2` `1 | 20 | 50` `2 | 200 | 400` `3 | 500 | 650` If I have a value of 25, how can i select records where the value of 25 is between them which in this case ...
I have the following method I need to ORDER BY: def has_attachments? attachments.size > 0 || (!parent.nil? && parent.has_attachments?) end I have gotten this far: ORDER BY CASE WHEN attachments.size > 0 THEN 1 ELSE (CASE WHEN parent_id IS NULL THEN 0 ELSE (CASE message.parent ...what goes here ) ...
There is a wizard that can contain over 150 questions or just 10. Each question can be different from the question asked before. For example, it is possible that one question requires you to answer a "Yes / No" answer, but the next one can contain a multiple choice list with four options. There also should be an opportunity to fill in a ...
I have a table tblUser , there is userId,firstName,LastName,Mobile,.......,QuestionID. Another table it's name tblResults There is questionID,Question,choiceID,choice,......... EG: tblUser userID - FirstName- LstName -Mobile ... ... ... - QuestionID - ChiceID 001 - xx - yy - 03212 - ...
I am running SQL Server 2005 but I am unsure which edition this is. How can I decide what edition (Express, Standard, Enterprise etc) is running on the machine? ...
I know SQL but wanted to master it for use in complex SQL and try to create complex SQL statement in one query. Any book to recommend. I found this book: SQL Design Patterns by Vadim Tropashko. What do you folks suggest? ...
I'm running IB2009 and I'm trying to count the number of records where a specific field is neither NULL nor empty: SELECT COUNT(A.ID) FROM MYVIEW A WHERE ((A.VARCHARFIELD1 IS NOT NULL) OR (A.VARCHARFIELD1 <> '')) where MYVIEW is a VIEW, and MYVIEW.ID is an INTEGER, while MYVIEW.VARCHARFIELD1 is a VARCHAR(18). I'm getting the error me...
I have two tables : T_STOCK: primary key is id, seller, and some others fields let say a and b. T_FLOW: primary key is (id + startdate), and some others fields, for example c and d. I want a query that returns all the columns for each records from T_STOCK regarding a specific seller, but completed with the columns (startDate, c and d) ...