Search Results

Search found 28900 results on 1156 pages for 'sql 2005'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 377/1156 | < Previous Page | 373 374 375 376 377 378 379 380 381 382 383 384  | Next Page >

  • Nesting queries in SQL

    - by ZAX
    The goal of my query is to return the country name and its head of state if it's headofstate has a name starting with A, and the capital of the country has greater than 100,000 people utilizing a nested query. Here is my query: SELECT country.name as country, (SELECT country.headofstate from country where country.headofstate like 'A%') from country, city where city.population > 100000; I've tried reversing it, placing it in the where clause etc. I don't get nested queries. I'm just getting errors back, like subquery returns more than one row and such. If someone could help me out with how to order it, and explain why it needs to be a certain way, that'd be great.

    Read the article

  • LINQ to SQL filter combobox output

    - by Brendan
    OK so I've got 2 tables for this instance, Users{UserID, Name}, Company{CompanyID, UserID, Name, Payrate} i also have 2 combo boxes, first one is for Users which Displays Name, and the Value is UserID i need the second combobox to get the Names from the Company table, but only showing Companies that are relevant to the selected user. I cant work out how to get it to go... Any ideas???

    Read the article

  • SQL CASE Question

    - by docsql
    Hiya, I dont know if this can be done but i'd though i'd ask. What I want to do is have a case statement query and if a 1 begin another action. if 0 dont do anything. For Example select CASE WHEN client.deathofdeath = yes THEN 1 do another select in here (which is another table) Else 0 End AS DeathDate From Client client Can this be done?

    Read the article

  • how to insert based on the date

    - by Gaolai Peng
    I have a table table1 (account, last_contact_date, insert_date), account and last_contact_date are primary keys. The insert_date is set with the time the recored being added by calling getdate(). I also have a temporary table #temp(account, last_contact_date) which I use to update the table1. Here are sample data: table1 account last_contact_date insert_date 1 2012-09-01 2012-09-28 2 2012-09-01 2012-09-28 3 2012-09-01 2012-09-28 #temp account last_contact_date 1 2012-09-27 2 2012-09-27 3 2012-08-01 The result table depends on the inserting date. If the date is 2012-09-28, the result will be table1 account last_contact_date insert_date 1 2012-09-27 2012-09-28 2 2012-09-27 2012-09-28 3 2012-09-01 2012-09-28 If the date is 2012-09-29, the result will be table1 account last_contact_date insert_date 1 2012-09-01 2012-09-28 2 2012-09-01 2012-09-28 3 2012-09-01 2012-09-28 1 2012-09-27 2012-09-29 2 2012-09-27 2012-09-29 Basically the rule is (1) if the inserting date is the same day, i will pick the lastest last_contact_date, otherwise, (2) if the last_contact_date is later than the current last_contact_date, I will insert a new one. How do I write a query for this insert?

    Read the article

  • SQL query root parent child records

    - by Vish
    Hi, We have nested folders with parent-child relationship. We use MySQL MyISAM DB. The data is stored in the DB in the following manner. Every time a child folder is created in the nested structure, the previous parentID is added. I want to get the RootFolderID of a folder which is added in the hierarchy as tabulated below. FoldID ParentID |RootFolderID -----------------|------------------- 1 0 | 0 2 1 | 1 3 2 | 1 4 3 | 1 5 4 | 1 Please let me know how to get the root folderID and populate it in the RootFolderID column after a folder is created each time. Thanks.

    Read the article

  • Date range intersection in SQL

    - by Will
    I have a table where each row has a start and stop date-time. These can be arbitrarily short or long spans. I want to query the sum duration of the intersection of all rows with two start and stop date-times. How can you do this in MySQL? Or do you have to select the rows that intersect the query start and stop times, then calculate the actual overlap of each row and sum it client-side?

    Read the article

  • Sql server 2008 query

    - by Prashant
    I am trying to implement versioning of data I have two tables Client and Address. I have to display in the UI, the various updates in the order in which they were made but with the correct client version so, Client Table Address Table ---------- ---------- Client Version Modified Date Address Version ModifiedDate CV1 T1 AV1 T2 CV2 T4 AV2 T3 CV3 T5 My result should be CV1 AV1 (first version) CV1 AV2 (as AV1 was updated at T3) CV2 AV2 (as Client got updated to CV2 at T4) CV3 AV2 (As client has got updated at T5)

    Read the article

  • SQL Update to the SUM of its joined values

    - by CL4NCY
    Hi, I'm trying to update a field in the database to the sum of its joined values: UPDATE P SET extrasPrice = SUM(E.price) FROM dbo.BookingPitchExtras AS E INNER JOIN dbo.BookingPitches AS P ON E.pitchID = P.ID AND P.bookingID = 1 WHERE E.[required] = 1 When I run this I get the following error: "An aggregate may not appear in the set list of an UPDATE statement." Any ideas?

    Read the article

  • How to exclude rows where matching join is in an SQL tree

    - by Greg K
    Sorry for the poor title, I couldn't think how to concisely describe this problem. I have a set of items that should have a 1-to-1 relationship with an attribute. I have a query to return those rows where the data is wrong and this relationship has been broken (1-to-many). I'm gathering these rows to fix them and restore this 1-to-1 relationship. This is a theoretical simplification of my actual problem but I'll post example table schema here as it was requested. item table: +------------+------------+-----------+ | item_id | name | attr_id | +------------+------------+-----------+ | 1 | BMW 320d | 20 | | 1 | BMW 320d | 21 | | 2 | BMW 335i | 23 | | 2 | BMW 335i | 34 | +------------+------------+-----------+ attribute table: +---------+-----------------+------------+ | attr_id | value | parent_id | +---------+-----------------+------------+ | 20 | SE | 21 | | 21 | M Sport | 0 | | 23 | AC | 24 | | 24 | Climate control | 0 | .... | 34 | Leather seats | 0 | +---------+-----------------+------------+ A simple query to return items with more than one attribute. SELECT item_id, COUNT(DISTINCT(attr_id)) AS attributes FROM item GROUP BY item_id HAVING attributes > 1 This gets me a result set like so: +-----------+------------+ | item_id | attributes | +-----------+------------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | -- etc. -- However, there's an exception. The attribute table can hold a tree structure, via parent links in the table. For certain rows, parent_id can hold the ID of another attribute. There's only one level to this tree. Example: +---------+-----------------+------------+ | attr_id | value | parent_id | +---------+-----------------+------------+ | 20 | SE | 21 | | 21 | M Sport | 0 | .... I do not want to retrieve items in my original query where, for a pair of associated attributes, they related like attributes 20 & 21. I do want to retrieve items where: the attributes have no parent for two or more attributes they are not related (e.g. attributes 23 & 34) Example result desired, just the item ID: +------------+ | item_id | +------------+ | 2 | +------------+ How can I join against attributes from items and exclude these rows? Do I use a temporary table or can I achieve this from a single query? Thanks.

    Read the article

  • Choosing proper database for a few users application

    - by tomo
    Requirements: tiny WinForms client app (C# 4.0, WinForms or WPF) a few users working simultinausly no database service at all - the whole engine as *.DLLs inside client apps database available as shared folder on one computer at least simple concurrrency checks compatible with nHibernate or EntityFramework / NET 4.0 backup as simple as copying files from shared folder - assuming no running clients at the moment no stored procedures/triggers required data size - a few tables and a few thousands rows after 2 years Nice to have: user access rights encrypted data I'm trying to choose between: MS Access SqlLite SqlServer Compact Edition. Can you recommend which one should be the best for these requirements?

    Read the article

  • Calculate the SUM of the Column which has Time DataType:

    - by thevan
    I want to calculate the Sum of the Field which has Time DataType. My Table is Below: TableA: TotalTime ------------- 12:18:00 12:18:00 Here I want to sum the two time fields. I tried the below Query SELECT CAST( DATEADD(MS, SUM(DATEDIFF(MS, '00:00:00.000', CONVERT(TIME, TotalTime))), '00:00:00.000' ) AS TOTALTIME) FROM [TableA] But it gives the Output as TOTALTIME ----------------- 00:36:00.0000000 But My Desired Output would be like below: TOTALTIME ----------------- 24:36:00 How to get this Output?

    Read the article

  • Keeping DB Table sorted using multi-field formula (Microsoft SQL)

    - by user298167
    Hello Everybody. I have a Job Table which has two interesting columns: Creation Date and Importance (high - 3, medium 2, low - 1). Job's priority calculated like this: Priority = Importance * (time passed since creation). The problem is, Every time I would like to pick 200 jobs with highest priority, I dont want to resort the table. Is there a way to keep rows sorted? I was also thinking about having three tables one for High, Medium and Low and then sort those by Creation Date. Thanks

    Read the article

  • how to enter manual time stamp in get date ()

    - by Arunachalam
    how to enter manual time stamp in get date () ? select conver(varchar(10),getdate(),120) returns 2010-06-07 now i want to enter my own time stamp in this like 2010-06-07 10.00.00.000 i m using this in select * from sample table where time_stamp ='2010-06-07 10.00.00.000' since i m trying to automate this query i need the current date but i need different time stamp can it be done .

    Read the article

  • sql insert query needed

    - by masfenix
    Hey guys, so I have two tables. They are pictured below. I have a master table "all_reports". And a user table "user list". The master table may have users that do not exist in the user list. I need to add them to the user list. The master table may have duplicates in them (check picture). The master list does not contain all the information that the user list requires (no manager, no HR status, no department.. again check picture).

    Read the article

  • getting sql records

    - by droidus
    when i run this code, it returns the topic fine... $query = mysql_query("SELECT topic FROM question WHERE id = '$id'"); if(mysql_num_rows($query) > 0) { $row = mysql_fetch_array($query) or die(mysql_error()); $topic = $row['topic']; } but when I change it to this, it doesn't run at all. why is this happening? $query = mysql_query("SELECT topic, lock FROM question WHERE id = '$id'"); if(mysql_num_rows($query) > 0) { $row = mysql_fetch_array($query) or die(mysql_error()); $topic = $row['topic']; $lockedThread = $row['lock']; echo "here: " . $lockedThread; }

    Read the article

  • SQL to get friends AND friends of friends of a user

    - by Enrique
    My MySQL tables structure is like this. USER int id varchar username FRIEND_LIST int user_id int friend_id For each friend relationship I insert 2 records in FRIEND_LIST. If user 1 is friend of user 2 then the next rows are inserted into FRIEND_LIST 1,2 2,1 I want to get the friends and friends of friends of an specific user. The select should return columns a, b, c. a: user_id b: friend_id c: username (username of friend_id ) If 1 is friend of 2 and 3. 2 is friend of 3, 4 and 5 3 is friend of 5,6,7 Then the query to get 1's friends and friends of friends should return: 1 2 two 1 3 three 2 1 one 2 3 three 2 4 four 2 5 five 3 1 one 3 5 five 3 6 six 3 7 seven Can I get this rows with a single query?

    Read the article

  • SQL DataReader how to show null-values from query

    - by cc0
    I have a DataReader and a StringBuilder (C#.NET) used in the following way; while (reader.Read()) { sb.AppendFormat("{0},{1},{2},",reader["Col1"], reader["Col2"], reader["Col3"]); } Which works great for my use, but when a row is null I need it to return "null", instead of just "". What would be a good way of accomplishing that? Suggestions are very appreciated

    Read the article

  • Best way to return result from business layer to presentation layer when using LINQ-to-SQL

    - by samsur
    I have a business layer that has DTOs that are used in the presentation layer. This application uses entity framework. Here is an example of a class called RoleDTO: public class RoleDTO { public Guid RoleId { get; set; } public string RoleName { get; set; } public string RoleDescription { get; set; } public int? OrganizationId { get; set; } } In the BLL I want to have a method that returns a list of DTO. I would like to know which is the better approach: returning IQueryable or list of DTOs. Although I feel that returning IQueryable is not a good idea because the connection needs to be open. Here are the 2 different methods using the different approaches: First approach public class RoleBLL { private servicedeskEntities sde; public RoleBLL() { sde = new servicedeskEntities(); } public IQueryable<RoleDTO> GetAllRoles() { IQueryable<RoleDTO> role = from r in sde.Roles select new RoleDTO() { RoleId = r.RoleID, RoleName = r.RoleName, RoleDescription = r.RoleDescription, OrganizationId = r.OrganizationId }; return role; } Note: in the above method the DataContext is a private attribute and set in the constructor, so that the connection stays opened. Second approach public static List<RoleDTO> GetAllRoles() { List<RoleDTO> roleDTO = new List<RoleDTO>(); using (servicedeskEntities sde = new servicedeskEntities()) { var roles = from pri in sde.Roles select new { pri.RoleID, pri.RoleName, pri.RoleDescription }; //Add the role entites to the DTO list and return. This is necessary as anonymous types can be returned acrosss methods foreach (var item in roles) { RoleDTO roleItem = new RoleDTO(); roleItem.RoleId = item.RoleID; roleItem.RoleDescription = item.RoleDescription; roleItem.RoleName = item.RoleName; roleDTO.Add(roleItem); } return roleDTO; } } Please let me know, if there is a better approach.

    Read the article

  • SQL-Server: Impersonation

    - by Falcon
    Hello, is there any way to make a certain session execute all commands as a certain user? I cannot use the execute as clause because it mustn't be hardcoded. I need something along the lines of this pseudocode: ALTER SESSION sessionid SET EXECUTING_USER=someuser

    Read the article

  • Is it a good idea to use a computed column as part of a primary key ?

    - by Brann
    I've got a table defined as : OrderID bigint NOT NULL, IDA varchar(50) NULL, IDB bigint NULL, [ ... 50 other non relevant columns ...] The natural primary key for this table would be (OrderID,IDA,IDB), but this it not possible because IDA and IDB can be null (they can both be null, but they are never both defined at the same time). Right now I've got a unique constraint on those 3 columns. Now, the thing is I need a primary key to enable transactional replication, and I'm faced with two choices : Create an identity column and use it as a primary key Create a non-null computed column C containing either IDA or IDB or '' if both columns were null, and use (OrderID,C) as my primary key. The second alternative seams cleaner as my PK would be meaningful, and is feasible (see msdn link), but since I've never seen this done anywhere, I was wondering if they were some cons to this approach.

    Read the article

  • Custom Grid LINQ to SQL help

    - by user488361
    Following is my custome cotrol grid... public partial class LinqGrid : UserControl { object tmpDataTable = new object(); public LinqGrid() { InitializeComponent(); } public void Bind(System.Data.Linq.Table listSource) where T : class { Project.dbClassesDataContext dbc = new Project.dbClassesDataContext(); tmpDataTable = listSource; var query = (from c in listSource select c); dgvRecords.DataSource = query.Take(10).ToList(); } private void btnNext_Click(object sender, EventArgs e) { // now what i have to do here if i want next 10 records.....means how to retrive tmpDataTable object here... ??? i can't find Type of variable....?? plz help me.... } }

    Read the article

  • LINQ to SQL: Reusable expression for property?

    - by coenvdwel
    Pardon me for being unable to phrase the title more exact. Basically, I have three LINQ objects linked to tables. One is Product, the other is Company and the last is a mapping table Mapping to store what Company sells which products and by which ID this Company refers to this Product. I am now retrieving a list of products as follows: var options = new DataLoadOptions(); options.LoadWith<Product>(p => p.Mappings); context.LoadOptions = options; var products = ( from p in context.Products select new { ProductID = p.ProductID, //BackendProductID = p.BackendProductID, BackendProductID = (p.Mappings.Count == 0) ? "None" : (p.Mappings.Count > 1) ? "Multiple" : p.Mappings.First().BackendProductID, Description = p.Description } ).ToList(); This does a single query retrieving the information I want. But I want to be able to move the logic behind the BackendProductID into the LINQ object so I can use the commented line instead of the annoyingly nested ternary operator statements for neatness and re-usability. So I added the following property to the Product object: public string BackendProductID { get { if (Mappings.Count == 0) return "None"; if (Mappings.Count > 1) return "Multiple"; return Mappings.First().BackendProductID; } } The list is still the same, but it now does a query for every single Product to get it's BackendProductID. The code is neater and re-usable, but the performance now is terrible. What I need is some kind of Expression or Delegate but I couldn't get my head around writing one. It always ended up querying for every single product, still. Any help would be appreciated!

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 373 374 375 376 377 378 379 380 381 382 383 384  | Next Page >