I can't seem to find a relevant example out there. I'm trying to return a sub-set of a table, and for each row in that table, I want to check how many children it has, and return that number as part of the result set. Parent Table Columns: PK_ID, Column1, Column2, FK1 For each FK1 in result set, select count(*) from child_table. Fina...
I am newbie to db programming and need help with optimizing this query: Given tables A, B and C and I am interested in one column from each of them, how to write a query such that I can get one column from each table into 3 different arrays/lists in my C# code? I am currently running three different queries to the DB but want to accomp...
I have a question related to gridviews in ASP.NET I have a gridview that I pull information from a SQL db and it displays the username and current status IE; Username | Status user1 | Logged Out user2 | Logged in user3 | On Leave So this is working all handy dandy, however what I'd like to do is rather that display ...
Is it beneficial to add an index to a column that is part of a foreign key relationship? I have two columns which will be queried frequently and already have them foreign keyed but wasn't sure if I should index them aswell, or if the foreign key creates an index behind the scenes? ...
While entering an SQL statement in Oracle SQL Developer, I noticed that I have two choises. I can either "Execute Statement" or "Run Script". A similar choise seems to be available in SQL Maestro as well, altough named "Execute query" and "Execute as script". What exactly is the difference between the two? ...
I've got data in ten minutes intervals in my table: 2009-01-26 00:00:00 12 2009-01-26 00:10:00 1.1 2009-01-26 00:20:00 11 2009-01-26 00:30:00 0 2009-01-26 00:40:00 5 2009-01-26 00:50:00 3.4 2009-01-26 01:00:00 7 2009-01-26 01:10:00 7 2009-01-26 01:20:00 7.2 2009-01-26 01:30:00 3 2009-01-...
I'm working on a SQL Reporting Services report (in VS.Net 2005) which displays a count of different data in a matrix. The columns have a count on the amount of customers in a certain set. So I have several columns like these: "1 employer", "2-9 employers", "10-19 employers" and so on. The problem I have is that SQL Reporting Services so...
Hi there, I know there is a way to upload images to the database as image type or varbinary type, however, I searched around the entire week, I am unable to find anything that can help me, so this is really my last resort, if anybody know how to upload images to the database, I am using SQL Server 2005 Express. Thanks ...
I am trying to insert a .csv file into a database with unix linebreaks. The command I am running is: BULK INSERT table_name FROM 'C:\file.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) If I convert the file into Windows format the load works, but I don't want to do this extra step if it can be avoided. Any id...
I'm currently in charge of a process that seems to be very intimate with the database. My program/script/framework's goal is to make uniformity out of disparate data sources. Using a form of dependency injection, my process at a very high level works fine. The implementation of each data source type is hidden from the highest level busin...
Assume I have two data tables and a linking table as such: A B A_B_Link ----- ----- ----- ID ID A_ID Name Name B_ID 2 Questions: I would like to write a query so that I have all of A's columns and a count of how many B's are linked to A, what is the best way to do this? Is...
Let's say I have two tables, "Parent" and "Child". Parent-to-Child is a many:many relationship, implemented through a standard cross-referencing table. I want to find all records of Parent that are referenced by ALL members of a given set of Child using SQL (in particular MS SQL Server's T-SQL; 2005 syntax is acceptable). For example l...
Are Select and SelectMany preferrable to Joins? The reason I'm wondering is because I use LinqPad and in one section there are comments that say: // Note: before delving into this section, make sure you've read the preceding two // sections: Select and SelectMany. The Join operators are actually unnecessary // in LINQ to SQL, and the e...
I have a Stored Proc that do a validation on a parameter ex. IF @SearchType = 'BNa' BEGIN ... DO something END ELSE IF @SearchType = 'SNa' BEGIN ... DO something END So by default the Stored Proc return a scalar value and if SearchType = something valid it will return a IMultipleValues. The problem is that when I drop my Sto...
Hi. I am a beginner SQL user (not formally trained; OJT only) and need some assistance with a simple update statement. I would like to write an update statement that allows me to list ID's. The statement shown below is how I am currently writing it. Ideally, the statement would allow me to write it like "where plantunitid in (49-57)....
Does something like this affect performance badly? var myQuery = from c in Customers select c; var filter1 = from c in myQuery where c.ID > 2 select c; myQuery = filter1; var filter2 = from c in myQuery where c.Name.Contains("r") select c; myQuery = filter2; When I do this it seems to only do the actual query at the end, not on ev...
For example, in my WCF, if I have a Customer table and an Order table that have an association to each other on Customer ID. I want to get the Count of the number of Orders for each Customer. In regular VB code I can just do this: Dim CustID As Integer = 1234 Dim cust As New MyService.Customer() cust = cust.GetCustomer(CustID) Respons...
I've gotten a request to show a person's local time based on their phone number. I know our local GMT offset, so I can handle USA phones via a database table we have that links US zip_code to GMT offset (e.g. -5). But I've no clue how to convert non-US phone numbers or country names (these people obviously don't have a zip code). If y...
I've got Dim Tables, Fact Tables, ETL and a cube. I'm now looking to make sure my cube only holds the previous 2 months worth of data. Should this be done by forcing my fact table to hold only 2 months of data and doing a "full process", or is there a way to trim outdated data from my cube? ...
There are three tables, Students, Classes, Student_Class Is it possible to select students who are not registered in any classes without using NOT IN? e.g. Select studentname from Students Where studentid NOT IN (Select Distinct studentid From Student_Class) ...