Search Results

Search found 28052 results on 1123 pages for 't sql tuesday'.

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 675/1123 | < Previous Page | 671 672 673 674 675 676 677 678 679 680 681 682  | Next Page >

  • Database Optimization techniques for amateurs.

    - by Zombies
    Can we get a list of basic optimization techniques going (anything from modeling to querying, creating indexes, views to query optimization). It would be nice to have a list of these, one technique per answer. As a hobbyist I would find this to be very useful, thanks. And for the sake of not being too vague, let's say we are using a maintstream DB such as MySQL or Oracle, and that the DB will contain 500,000-1m or so records across ~10 tables, some with foreign key contraints, all using the most typical storage engines (eg: InnoDB for MySQL). And of course, the basics such as PKs are defined as well as FK contraints.

    Read the article

  • How do I specify a default value in a MS Access query?

    - by jheddings
    I have three tables similar to the following: tblInvoices: Number | Date | Customer tblInvDetails: Invoice | Quantity | Rate | Description tblPayments: Invoice | Date | Amount I have created a query called exInvDetails that adds an Amount column to tblInvDetails: SELECT tblInvDetails.*, [tblInvDetails.Quantity]*[tblInvDetails.Rate]* AS Amount FROM tblInvDetails; I then created a query exInvoices to add Total and Balance columns to tblInvoices: SELECT tblInvoices.*, (SELECT Sum(exInvDetails.Amount) FROM exInvDetails WHERE exInvDetails.Invoice = tblInvoices.Number) AS Total, (SELECT Sum(tblPayments.Amount) FROM tblPayments WHERE tblPayments.Invoice = tblInvoices.Number) AS Payments, (Total-Payments) AS Balance FROM tblInvoices; If there are no corresponding payments in tblPayments, the fields are null instead of 0. Is there a way to force the resulting query to put a 0 in this column?

    Read the article

  • Create an index only on certain rows in mysql

    - by dhruvbird
    So, I have this funny requirement of creating an index on a table only on a certain set of rows. This is what my table looks like: USER: userid, friendid, created, blah0, blah1, ..., blahN Now, I'd like to create an index on: (userid, friendid, created) but only on those rows where userid = friendid. The reason being that this index is only going to be used to satisfy queries where the WHERE clause contains "userid = friendid". There will be many rows where this is NOT the case, and I really don't want to waste all that extra space on the index. Another option would be to create a table (query table) which is populated on insert/update of this table and create a trigger to do so, but again I am guessing an index on that table would mean that the data would be stored twice. How does mysql store Primary Keys? I mean is the table ordered on the Primary Key or is it ordered by insert order and the PK is like a normal unique index? I checked up on clustered indexes (http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html), but it seems only InnoDB supports them. I am using MyISAM (I mention this because then I could have created a clustered index on these 3 fields in the query table). I am basically looking for something like this: ALTER TABLE USERS ADD INDEX (userid, friendid, created) WHERE userid=friendid

    Read the article

  • How can I get the count of orders placed from my database?

    - by user1360564
    I am preparing a chart which will display the number of orders placed for a particular day in the current month and year. I wanted the count of orders placed for each day. I am showing the count of orders on the y-axis and the day on the x-axis. In my database, there is table called "order" in which order data is placed: order date, user_id, order_price, etc. For example, if on 4 July, 10 orders are placed, on 5 july, 20 orders are placed, and so on. How can I get the count of orders placed for day of the current month?

    Read the article

  • Help with query in Microsoft Access

    - by Gold
    I have 2 tables: Table A: code | name Table B: barcode | name Table B has full barcode and name, Table A has only code. I need to run update query that fill name in Table A. I tried something like: update A set name = (select top 1 Name from B where B.Code = mid(A.Barcode,1,8)) but it doesn't work.

    Read the article

  • How to use php variables (arrays) in mysql select statements?

    - by davidconn
    Hi everybody, How do you use a php variable (array) inside a mysql select statement? I am designing an auction site and currently working on a page that lets people view a list of all the current bids for an item. I want to display 3 columns: amountbid - the amount each bidder has bid for the item (held in tblbid) bidderid - the id of each bidder who has bid (found in tbluser) total_positivity_feedback - how many users have left positive feedback for the bidder (calculated from tblfeedback) To find the 'amount' and the 'bidderid' columns i pass the essayid URL parameter from the previous page. This works fine. Despite this, i can't display the total_positivity_feedback column for the various users who have made each bid. My mysql query looks like this: select tblbid.bidderid, tblbid.amount, (select count(tblfeedback.feedbackid) from tblfeedback WHERE tblfeedback.writerid = "ARRAY VARIABLE GOES HERE") AS total_positivity_feedback FROM tblbid WHERE tblbid.essayid = $essayid_bids I assume that the only way to accomplish this is to make the variable contain the bidderid's of those people who bid for that particular essay? I can't seem to work out how to do this tho?!? Many thanks for your help

    Read the article

  • LinqtoSql Pre-compile Query problem with Count() on a group by

    - by Joe Pitz
    Have a LinqtoSql query that I now want to precompile. var unorderedc = from insp in sq.Inspections where insp.TestTimeStamp > dStartTime && insp.TestTimeStamp < dEndTime && insp.Model == "EP" && insp.TestResults != "P" group insp by new { insp.TestResults, insp.FailStep } into grp select new { FailedCount = (grp.Key.TestResults == "F" ? grp.Count() : 0), CancelCount = (grp.Key.TestResults == "C" ? grp.Count() : 0), grp.Key.TestResults, grp.Key.FailStep, PercentFailed = Convert.ToDecimal(1.0 * grp.Count() / tcount * 100) }; I have created this delegate: public static readonly Funct<SQLDataDataContext, int, string, string, DateTime, DateTime, IQueryable<CalcFailedTestResult>> GetInspData = CompiledQuery.Compile((SQLDataDataContext sq, int tcount, string strModel, string strTest, DateTime dStartTime, DateTime dEndTime, IQueryable<CalcFailedTestResult> CalcFailed) => from insp in sq.Inspections where insp.TestTimeStamp > dStartTime && insp.TestTimeStamp < dEndTime && insp.Model == strModel && insp.TestResults != strTest group insp by new { insp.TestResults, insp.FailStep } into grp select new { FailedCount = (grp.Key.TestResults == "F" ? grp.Count() : 0), CancelCount = (grp.Key.TestResults == "C" ? grp.Count() : 0), grp.Key.TestResults, grp.Key.FailStep, PercentFailed = Convert.ToDecimal(1.0 * grp.Count() / tcount * 100) }); The syntax error is on the CompileQuery.Compile() statement It appears to be related to the use of the select new {} syntax. In other pre-compiled queries I have written I have had to just use the select projection by it self. In this case I need to perform the grp.count() and the immediate if logic. I have searched SO and other references but cannot find the answer.

    Read the article

  • Consolidating separate Loan, Purchase & Sales tables into one transaction table.

    - by Frank Computer
    INFORMIX-SE with ISQL 7.3: I have separate tables for Loan, Purchase & Sales transactions. Each tables rows are joined to their respective customer rows by: customer.id [serial] = loan.foreign_id [integer]; = purchase.foreign_id [integer]; = sale.foreign_id [integer]; I would like to consolidate the three tables into one table called "transaction", where a column "transaction.trx_type" [char(1)] {L=Loan, P=Purchase, S=Sale} identifies the transaction type. Is this a good idea or is it better to keep them in separate tables? Storage space is not a concern, I think it would be easier programming & user=wise to have all types of transactions under one table.

    Read the article

  • Why is it possible to enumerate a LinqToSql query after calling Dispose() on the DataContext?

    - by DanM
    I'm using the Repository Pattern with some LinqToSql objects. My repository objects all implement IDisposable, and the Dispose() method does only thing--calls Dispose() on the DataContext. Whenever I use a repository, I wrap it in a using person, like this: public IEnumerable<Person> SelectPersons() { using (var repository = _repositorySource.GetNew<Person>(dc => dc.Person)) { return repository.GetAll(); } } This method returns an IEnumerable<Person>, so if my understanding is correct, no querying of the database actually takes place until Enumerable<Person> is traversed (e.g., by converting it to a list or array or by using it in a foreach loop), as in this example: var persons = gateway.SelectPersons(); // Dispose() is fired here var personViewModels = ( from b in persons select new PersonViewModel { Id = b.Id, Name = b.Name, Age = b.Age, OrdersCount = b.Order.Count() }).ToList(); // executes queries In this example, Dispose() gets called immediately after setting persons, which is an IEnumerable<Person>, and that's the only time it gets called. So, a couple questions: How does this work? How can a disposed DataContext still query the database for results when I walk the IEnumerable<Person>? What does Dispose() actually do? I've heard that it is not necessary (e.g., see this question) to dispose of a DataContext, but my impression was that it's not a bad idea. Is there any reason not to dispose of it?

    Read the article

  • Sorting nested set by name while keep depth integrity

    - by wb
    I'm using the nested set model that'll later be used to build a sitemap for my web site. This is my table structure. create table departments ( id int identity(0, 1) primary key , lft int , rgt int , name nvarchar(60) ); insert into departments (lft, rgt, name) values (1, 10, 'departments'); insert into departments (lft, rgt, name) values (2, 3, 'd'); insert into departments (lft, rgt, name) values (4, 9, 'a'); insert into departments (lft, rgt, name) values (5, 6, 'b'); insert into departments (lft, rgt, name) values (7, 8, 'c'); How can I sort by depth as well as name? I can do select replicate('----', count(parent.name) - 1) + ' ' + node.name , count(parent.name) - 1 as depth , node.lft from departments node , departments parent where node.lft between parent.lft and parent.rgt group by node.name, node.lft order by depth asc, node.name asc; However, that does not match children with their parent for some reason. department lft rgt --------------------------- departments 0 1 ---- a 1 4 ---- d 1 2 -------- b 2 5 -------- c 2 7 As you can see, department 'd' has department 'a's children! Thank you.

    Read the article

  • Join with table and sub query in oracle

    - by Amandeep
    I dont understand what is wrong with this query it is giving me compile time error of command not ended properly.The inner query is giving me 4 records can any body help me out. select WGN3EVENTPARTICIPANT.EVENTUID from (Select WGN_V_ADDRESS_1.ADDRESSUID1 as add1, WGN_V_ADDRESS_1.ADDRESSUID2 as add2 from WGN3USER inner join WGN_V_ADDRESS_1 on WGN_V_ADDRESS_1.USERID=wgn3user.USERID where WGN3USER.USERNAME='FIRMWIDE\khuraj' ) as ta ,WGN3EVENTPARTICIPANT where (ta.ADDRESSUID1=WGN3EVENTPARTICIPANT.ADDRESSUID1) AND (ta.ADDRESSUID2=WGN3EVENTPARTICIPANT.ADDRESSUID2) I am running it in oracle. Thanks Amandeep

    Read the article

  • Create a trigger that updates a column on one table when a column in another table is updated

    - by GigaPr
    Hi, i have two tables Order(id, date, note) and Delivery(Id, Note, Date) I want to create a trigger that updates the date in Delivery when the date is updated in Order. I was thinking to do something like CREATE OR REPLACE TRIGGER your_trigger_name BEFORE UPDATE ON Order DECLARE BEGIN UPDATE Delivery set date = ??? where id = ??? END; How do I get the date and row id? thanks

    Read the article

  • MSSQL + Copy data from one server database table to the other

    - by lucky
    Hello All, I dont know how to copy table data from one server - database - table TO other sever - database - table. If it is with in the same server and different databases, i have used the following SELECT * INTO DB1..TBL1 FROM DB2..TBL1 (to copy with table structure and data) INSERT INTO DB1..TBL1(F1, F2) SELECT F1, F2 FROM DB2..TBL1 (copy only data) Now my question is copy the data from SERVER1 -- DB1-- TBL1 to SERVER2 -- DB2 --TBL2 Thanks in advance!

    Read the article

  • php and SQL_CALC_FOUND_ROWS

    - by Lizard
    I am trying to add the SQL_CALC_FOUND_ROWS into a query (Please note this isn't for pagination) please note I am trying to add this to a cakePHP query the code I currently have is below: return $this->find('all', array( 'conditions' => $conditions, 'fields'=>array('SQL_CALC_FOUND_ROWS','Category.*','COUNT(`Entity`.`id`) as `entity_count`'), 'joins' => array('LEFT JOIN `entities` AS Entity ON `Entity`.`category_id` = `Category`.`id`'), 'group' => '`Category`.`id`', 'order' => $sort, 'limit'=>$params['limit'], 'offset'=>$params['start'], 'contain' => array('Domain' => array('fields' => array('title'))) )); Note the 'fields'=>array('SQL_CALC_FOUND_ROWS',' this obviously doesn't work as It tries to apply the SQL_CALC_FOUND_ROWS to the table e.g. SELECTCategory.SQL_CALC_FOUND_ROWS, Is there anyway of doing this? Any help would be greatly appreciated, thanks.

    Read the article

  • What is the maximum length of a string parameter to Stored procedure?

    - by padmavathi
    I have a string of length 1,44,000 which has to be passed as a parameter to a stored procedure which is a select query on a table. When a give this is in a query (in c# ) its working fine. But when i pass it as a parameter to stored procedure its not working. Here is my stored procedure where in i have declared this parameter as NVARCHAR(MAX) ------------------------------------------------------ set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [dbo].[ReadItemData](@ItemNames NVARCHAR(MAX),@TimeStamp as DATETIME) AS select * from ItemData where ItemName in (@ItemNames) AND TimeStamp=@TimeStamp --------------------------------------------------------------------- Here the parameter @ItemNames is a string concatinated with different names such as 'Item1','Item2','Item3'....etc. Can anyone tell what went wrong here? Thanks & Regards Padma

    Read the article

  • sports league database design

    - by John
    Hello, I'm developing a database to store statistics for a sports league. I'd like to show several tables: - league table that indicates the position of the team in the current and previous fixture - table that shows the position of a team in every fixture in the championship I have a matches table: Matches (IdMatch, IdTeam1, IdTeam2, GoalsTeam1, GoalsTeam2) Whith this table I can calculate the total points of every team based on the matches the team played. But every time I want to show the league table I have to calculate the points. Also I have a problem to calculate in which position classified a team in the last 10 fixtures cause I have to make 10 queries. To store the league table for every fixture in a database table is another approach, but every time I change a match already played I have to recalculate every fixture from there... Is there a better approach for this problem? Thanks

    Read the article

  • Merging some columns of two mysql tables where id = fileid

    - by garg
    There are two tables TableA filedata_id | user_id | filename 1 | 1 | file.txt 2 | 1 | file2.txt TableB a_id | date | filedataid | counter | state | cat_id | subcat_id | med_id 99 | 1242144 | 1 | 2 | v | 55 | 56 | 90 100 | 1231232 | 2 | 3 | i | 44 | 55 | 110 I want to move columns cat_id, subcat_id, med_id to TableA where tableA.filedata_id = TableB.filedataid The result should be: TableA filedata_id | user_id | filename | cat_id | subcat_id | med_id 1 | 1 | file.txt | 55 | 56 | 90 2 | 1 | file2.txt | 44 | 55 | 110 and so on. Is there a way to do this easily?

    Read the article

  • User activity vs. System activity on the Index Usage Statistics report

    - by Zachary G Jensen
    I recently decided to crawl over the indexes on one of our most heavily used databases to see which were suboptimal. I generated the built-in Index Usage Statistics report from SSMS, and it's showing me a great deal of information that I'm unsure how to understand. I found an article at Carpe Datum about the report, but it doesn't tell me much more than I could assume from the column titles. In particular, the report differentiates between User activity and system activity, and I'm unsure what qualifies as each type of activity. I assume that any query that uses a given index increases the '# of user X' columns. But what increases the system columns? building statistics? Is there anything that depends on the user or role(s) of a user that's running the query?

    Read the article

  • How to create festival calendar in ASP.net

    - by Atul
    I want to make a festival calendar using asp.net from that I used two ajax calendar and one textbox it is a festival textbox where we enter festival which FromDate and ToDate respectively. I want to do this as following point If I enter in textbox Christmas and Choose Fromdate=25/12/2011 and ToDate=31/12/2011 then it will be valid If I choose fromDate=25/12/2011 and ToDate=24/12/2011 then it will invalid If I choose Fromdate=25/12/2011 and Todate=28/12/2011 then also it is invalid because it coming in between 25/12/2011 and 31/12/2011 If I Choose fromdate=1/1/2011 and ToDate=1/1/2011 then it is valid If I choose fromdate=21/12/2011 and 25/12/2011 then it is invalid because of already Christmas done in 1/1/2011 And all date should show in gridview like 25-dec-2011 format Here is my code: DateTime dt1 = Convert.ToDateTime(txt_fromdate.Text); DateTime dt2 = Convert.ToDateTime(txt_todate.Text); if (dt1 > dt2) { con.Open(); com = new SqlCommand("BTNN_MovieDB_Festival_Details_Insert", con); com.Parameters.Add("@fromdate", SqlDbType.VarChar).Value = dateformat_mmdd(txt_fromdate.Text.ToString().Trim()); com.Parameters.Add("@todate", SqlDbType.VarChar).Value = dateformat_mmdd(txt_todate.Text.ToString().Trim()); com.Parameters.Add("@return", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue; com.ExecuteNonQuery(); con.Close(); showdata(); } else if (dt1 < dt2) { lblerror.Text = "ToDate should be greater than FromDate"; }

    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 | 671 672 673 674 675 676 677 678 679 680 681 682  | Next Page >