I have a table with columns ID, A, B, C, D, E... (10 numeric columns in all) For each row, I need to find which column has the largest value, and what that value is. E.G. Here's 1 row of my table: ID A B C D E F G H I J XY 5 4 9 5 0 1 3 2 1 7 I want to generate 2 new columns: maxvalue, which would equal 9, and ma...
This query works: SELECT u.UserId, u.GroupId, u.username, gp.PreferenceId, gp.Properties, gp.Override FROM dbo.UserTable u Inner JOIN dbo.GroupPreferences gp ON gp.GroupID = u.GroupId WHERE (u.UserName = 'myUserId') But the same query inside another query doesn't. I ge...
I'm working on a project in which we will need to determine certain types of statuses for a large body of people, stored in a database. The business rules for determining these statuses are fairly complex and may change. For example, if a person is part of group X and (if they have attribute O) has either attribute P or attribute Q,...
I've saw a join just like this: Select <blablabla> from TableA TA Inner join TableB TB on Ta.Id = Tb.Id Inner join TableC TC on Tc.Id = Tb.Id and Ta.OtheriD = Tc.OtherColumn But what's the point (end effect) of that second join clause? What the implications when an outer join clause is used? And, more important, what is the bes...
Can i use several WHEN conditions in UPDATE clause to update a single column. I want to update table TABLE having columns ID and NAME: Is below query correct? UPDATE TABLE SET id = CASE id WHEN id IN (2, 3, 4) THEN 1 WHEN id= 5 THEN 8 WHEN id IN(9, 7) THEN 6 WHERE name = 'abc' ...
I have an SQL table with the following design: TableSchedule: Id Description ImportedDate I can import a large number of items and they will all have the same ImportedDate. How can I write a LINQ query that grabs only the entries with the most recent ImportedDate? var ResultSchedule = from a in Db.TableSchedule where a.Impor...
I have a purchase form that has a continuous subform which shows line items for that purchase. Each line item needs to include a text box that shows the sum for a number of related records in a table for that line item. (Each line item is allocated for a specific purpose(s). For instance, a line item says that 100 widgets are ordered;...
===problem=== i'm using a LEFT JOIN, SQL expression, on 3 tables. i'm getting an unexpected error "JOIN expression not supported" from MS ACCESS 2007 when i try to run it. ===details=== these tables are all connected parent: is at the highest level child1: child of parent child2: child of parent grandchild1: child of child1 thi...
Hello, I have a SQL Server 2005 database with two tables: Order, LineItem. Each LineItem has a field called LineItemID and OrderID. I have a query that is getting all of the Order records in my database. With each Order record, I would like to retrieve a comma delimited list of LineItemIDs associated with the Order. Is there a way to d...
I have a query (source)... UPDATE covers t LEFT OUTER JOIN (SELECT cover_id FROM covers ORDER BY cover_views DESC LIMIT 10) c ON c.cover_id = t.cover_id SET cover_views = 0 WHERE c.cover_id IS NULL ...which resets all but the top 10 covers in the database to 0. I want to extend this to reset all covers bar the top ten per c...
I need a SP to return multiple sets of results. The second set of results would be based on a column of the first set of results. So: declare @myTable1 table(field0 int,field1 varchar(255)) insert into @myTable1 select top 1 field0, field1 from table1 declare @myTable2 table(field0 int,field3 varchar(255)) insert into @myTable2 selec...
I have a .NET 2010 app hitting a SQL2000 db. The code is pretty basic. When I insert a record, the record is inserted, but the id is not returned. The id column is an int and it is an Idetity. Here is the stored proc... ALTER PROCEDURE Insert_Vendor @CorpID as varchar(255), @TaxpayerID as varchar(255) AS Insert into dbo.Vendor ( vdr...
I need to have a table in T-SQL which will have the following structure KEY Various_Columns Flag 1 row 1 F 2 row_2 F 3 row_3 T 4 row_4 F Either no rows, or at most one row can have the Flag column with the value T. My developer claims...
Let's say I have the following tables: TAGS id: integer name: string POSTS id: integer body: text TAGGINGS id: integer tag_id: integer post_id: integer How would I go about writing a query that select all posts that are tagged with ALL of the following tags (name attribute of tags table): "Cheese", "Wine", "Paris", "Frace", "City"...
Let's say I have the following tables: TAGS id: integer name: string POSTS id: integer body: text TAGGINGS id: integer tag_id: integer post_id: integer How would I go about writing a query that selects all posts in order of the post containing the highest number of the following tags (name attribute of tags table): "Cheese", "Wine...
Hi all, I have two tables (A and B). I want to delete all the rows in Table B where B.1 isn't in Table A.2. So I wrote this formula in sqlite: DELETE FROM B WHERE 1 IN (SELECT * FROM B LEFT JOIN A ON A.1=B.2 WHERE A.1 IS NULL) But this returns this error: only a single result allowed for a SELECT that is part of ...
Hi there I get alot of help from this website.Thank Long live stack overflow team and contributors. My question is that is Lets say I have a c# desktop app running in client machine on network say at office and that particular application stores data in mdf database file. Backup of that database is placed on Server on that network as m...
I have the below SQL Query, which returns what I expect, and I would LIKE to accomplish the same thing in LINQ but so far my results have been less then spectacular. The major hurdle, as I see it, is that the data is coming from 3 separate DB's. I was able to accomplish this in LINQ but it is extremely slow, see here. So, with out fur...
I am storing in a column a list of states that are separated by commas: Like this: 1,2,3,4,5,6,7,8.. and so on, just their IDs. Then I am doing a query to get all the rows that have the state with the ID 8, and it works when the list of states has few items. This is the query and the table: mysql> select id_partner, name, states from...
Hi, I have problem with inner joining 2 tables. Table Lookup has only creative name and perf table has the creative name as well as the values to it. i need need to get the values into lookup table (where both the table has common creative name). Also, creative names have duplicates, so i need to sum them up and get the value. Below is...